1

I need to make updates to my WordPress DataBase but I keep hitting a roadblock when taking into consideration a secondary condition.

In very simplistic terms, I have a meta_key = 'flowers' that needs a meta_value = 'roses' to be set only where meta_key = 'month' and meta_value = 'august'.

I have not been able to write a successful MySQL query that includes the second condition. I know how to do this:

update wp_postmeta
  set meta_value = 'roses'
where meta_key = 'flowers'

but it is not limited to only meta_key = 'month' and meta_value = 'august'

Any help would be greatly appreciated! Thanks in advance.

UPDATE #1:

As requested by @Strawberry, to give further detail to my question. I am not entirely sure how but the meta_key = 'flowers' and the meta_key = 'month' must be related. As it stands, all posts consist of a meta_key = 'flowers' and the meta_key = 'month'.

UPDATE #2:

I was able to receive help from @Rahul, here is the version that worked for me:

UPDATE wp_postmeta a
JOIN wp_postmeta b ON a.post_id = b.post_id AND 
                      b.meta_key = 'month' AND 
                      b.meta_value = 'august'
  SET    a.meta_value = 'roses'
WHERE  a.meta_key = 'flowers';

Hopefully, it will help others as well. Also, for further detail on JOIN, see: How to do 3 table JOIN in UPDATE query?

Thanks to everyone!

Sami Ahmed Siddiqui
  • 2,328
  • 1
  • 16
  • 29
Jorge
  • 41
  • 2
  • 7

1 Answers1

0

The exists operator should do the job:

UPDATE wp_postmeta a
SET    a.meta_value = 'roses'
WHERE  a.meta_key = 'flowers' AND
       EXISTS (SELECT *
               FROM   wp_postmeta b
               WHERE  a.post_id = b.post_id AND 
                      b.meta_key = 'month' AND 
                      b.meta_value = 'august')
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • @Strawberry Hmm... good point :-) Edited my answer, that was dumb on my behalf. – Mureinik Sep 06 '15 at 16:28
  • I am not exactly sure how to answer your question. Are you referring to meta_key = flowers and meta_key = month being related? Is this where a post_id is required? – Jorge Sep 06 '15 at 16:30
  • @Jorge Well, yes - how else can we know that they are related - but this is too important a point to discuss in comments. Perhaps you could amend your question. – Strawberry Sep 06 '15 at 16:35
  • I executed the query and received the following error: #1093 - You can't specify target table 'a' for update in FROM clause – Jorge Sep 06 '15 at 18:24