-1

In my WordPress DB I am executing a version of this more simplified query:

    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')

I keep getting the following error:

   #1093 - You can't specify target table 'a' for update in FROM clause

I have limited experience in working with DB queries to understand how to fix the query.

Any help would be greatly appreciated.

UPDATE #1: The answer was provided below by @Rahul.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Jorge
  • 41
  • 2
  • 7

1 Answers1

1

Perform a UPDATE JOIN like below. Do a SELF JOIN with the same table.

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';
Rahul
  • 76,197
  • 13
  • 71
  • 125