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, Mar 12 '14 at 05:29
possible duplicate of [Mysql error 1093 - Can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — Barmar, Mar 12 '14 at 05:36

score 2 · Accepted Answer · answered Mar 12 '14 at 05:29

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 '14 at 05:29

Barmar

1 Answers1