Update Data In MySQL Gives Error

Question

update table1 set isDeleted = 1 where isDeleted = 0 and mId in (select id from table1 where isDeleted = 1 );

Error Code: 1093. You can't specify target table 'table1' for update in FROM clause

table1 id mid salary 1 null 10000 2 1 8000 3 1 8000 4 2 6000 update table1 set mid = t2.mid where mId in (select id from table1 as t2 where t2.mid is not null ); — user3386877
– user3386877, Commented Mar 12, 2014 at 5:29
possible duplicate of Mysql error 1093 - Can't specify target table for update in FROM clause — Barmar
– Barmar, Commented Mar 12, 2014 at 5:36

Barmar · Accepted Answer · 2014-03-12 05:29:57Z

2

Use a join:

UPDATE table1 t1 JOIN table1 t2 ON t1.mId = t2.id SET t1.isDeleted = 1 WHERE t1.isDeleted = 0 AND t2.isDeleted = 1

answered Mar 12, 2014 at 5:29

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1