I have a very specific issue that I'm unable to work out a solution to. It's related to WordPress, but that's for context and isn't otherwise relevant.
I have a table named wp_options. It's used to store various option information using 2 fields - option_name and option_value. These are, as they suggest, a unique name for the option and a value for that option.
This table is also used to store "transients" - temporary option values. These are stored as 2 records:
Record 1 has the name format of _transient_timeout_xxx, where xxx is the unique name. The value contains an expiry time.
Record 2 has the name format of _transient_xxx, where xxx is the unique name used as per record 1. The value contains the temporary option value.
What I want to do is to remove any transient records which have expired. Basically, hunt down any "record 1's" which have expired and to delete that and the matching record 2. But I can't work out how to do this withing a single MySQL statement. Is anybody more capable able to think of something?
Any help will be gratefully appreciated.