0

I got an "error '#1093 - You can't specify target table 't1' for update in FROM clause" with this code:

UPDATE `table1` t1 SET t1.name = 'abc'
WHERE t1.id = 165 AND NOT EXISTS (
    SELECT t2.id FROM `table1` t2 WHERE t2.id != 165 AND t2.name = 'abc'    
)

But there is no problem with the code:

UPDATE `table1` t1 SET t1.name = 'abc'
WHERE t1.id = 165 AND NOT EXISTS (
    SELECT t2.id FROM `table2` t2 WHERE t2.id != 165 AND t2.name = 'abc'    
)

What did I go wrong?

zontop
  • 3
  • 2
  • Possible duplicate of [How to resolve MySQL error "You can't specify target table X for update in FROM clause"?](https://stackoverflow.com/questions/37251621/how-to-resolve-mysql-error-you-cant-specify-target-table-x-for-update-in-from) – JYoThI Jul 06 '17 at 09:24

2 Answers2

1

You can rephrase this query as a self join to the same table:

UPDATE table1 t1
LEFT JOIN table1 t2
    ON t2.id != 165 AND t2.name = 'abc'
SET t1.name = 'abc'
WHERE t1.id = 165 AND t2.name IS NULL

The update logic here is that your NOT EXISTS clause has been replaced with a LEFT JOIN where any record from the left side of the join did not match (t2.name IS NULL) to any record where the id is not 165 and the name is abc.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

You can achieve same, without using inner query:

UPDATE `table1` t1 SET t1.name = 'abc'
WHERE t1.id = 165 AND t1.name != 'abc'
halfer
  • 19,824
  • 17
  • 99
  • 186
Sagar Gangwal
  • 7,544
  • 3
  • 24
  • 38