CODEWITHSUNDEEP
mysqlsql
zgall1
zgall1

Reputation: 3025

How to fix the error "You can't specify target table for update in FROM clause" when updating a SQL table

How do I rewrite the query below to avoid the error "You can't specify target table for update in FROM clause"?

UPDATE wp_postmeta
SET meta_value = meta_value + 'A' WHERE (SELECT post_title FROM wp_posts A
LEFT JOIN wp_postmeta B ON B.post_id = A.id WHERE A.post_type = 'player' AND
B.meta_key ='_yoast_wpseo_metadesc') = 'Eric Bledsoe'

Upvotes: 1

Views: 630

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

This is a MySQL limitation. You can use a join instead. This is one guess on what you intend with your query:

UPDATE wp_postmeta pm JOIN
       wp_posts p
       ON pm.post_id = p.id AND
          p.post_type = 'player' AND
          pm.meta_key ='_yoast_wpseo_metadesc'
    SET pm.meta_value = CONCAT(pm.meta_value, 'A')
WHERE p.post_title = 'Eric Bledsoe';

As mentioned in the comment, your query either updates all rows in wp_postmeta or none of them. The subquery has no correlation clause to the outer query.

Upvotes: 2

Related Questions