Update query error

In below mysql query i am getting error as:
You can’t specify target table ‘pt’ for update in FROM clause

update prop pt set pt.local_status=‘Deleted’ where pt.pt_ref IN (select p.pt_ref from prop p Left Join prop_import pi ON (p.pt_ref=pi.pt_ref) where pi.pt_ref IS NULL)

What am i doing wrong and how can i fix it?

From the manual (one line hidden in a page full of text :wink: ): http://dev.mysql.com/doc/refman/5.7/en/update.html

Currently, you cannot update a table and select from the same table in a subquery.

A possible work around can be found here: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

UPDATE tbl SET col = (SELECT ... FROM (SELECT.... FROM) AS x);

Didn’t try it myself, but using a subquery in the subquery apparently should create a temporary table and therefore avoid the conflict you’re encountering right now.

1 Like

UPDATE prop SET local_status = 'Deleted' WHERE NOT EXISTS ( SELECT 'whoa' FROM prop_import WHERE pt_ref = prop.pt_ref )

2 Likes

Many thanks for the replies :smile:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.