1

I am using following query to delete data which are not in another table.

delete from table_1 where tbl_id in  
(select tbl_id from table_1 left join table_2
on table_1.tbl_id=table_2.another_tbl_id where table_2.another_tbl_id is null)

Is there any issue in the above query?

It shows You can't specify target table 'table_1' for update in FROM clause.

How to solve this issue?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Babu R
  • 1,025
  • 8
  • 20
  • 40
  • There are numerous **Related** questions with this exact same error. Are you telling us that none of them have the solution? – Barmar May 20 '14 at 06:00
  • Yes. I checked all related questions. But not get cleared. – Babu R May 20 '14 at 06:02
  • So you tried using an `INNER JOIN` like in http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause?rq=1 and it didn't work? – Barmar May 20 '14 at 06:04
  • The [very first](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) related question I tried gave a full answer as to why your query does not work. You can't modify a table being used in a nested SELECT clause. – BonzaiThePenguin May 20 '14 at 06:05
  • You can use below mention query to remove duplicate data from user table behalf of user_id -( DELETE n1 FROM user n1, user n2 WHERE n1.id < n2.id AND n1.user_id = n2.user_id); – Umar Farooque Khan Feb 19 '20 at 08:59

4 Answers4

0

try this,

DELETE t1 FROM tablename1 t1 LEFT JOIN tablename2 tn2
ON t1.tbl_id=tn2.an`enter code here`other_tbl_id WHERE tn2.another_tbl_id IS NULL
ravikumar
  • 893
  • 1
  • 8
  • 12
0

You have to use a JOIN rather than IN

DELETE table_1.* FROM table_1
LEFT JOIN table_2 ON table_1.tbl_id=table_2.another_tbl_id
WHERE table_2.another_tbl_id IS NULL
Barmar
  • 741,623
  • 53
  • 500
  • 612
0

Solved my Problem.

I modified the above query as follows: 

delete from table_1 where tbl_id not in  
(select another_tbl_id from table_2)

Thanks for all your replies...

Babu R
  • 1,025
  • 8
  • 20
  • 40
  • This is in the second answer in the duplicate question, which you said you already checked. – Barmar May 20 '14 at 06:18
-1

Easiest way is here ....

        DELETE t1 FROM table_1 t1 WHERE t1.tbl_id NOT IN  
         (SELECT another_tbl_id FROM  table_2)
Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
Jumabek
  • 68
  • 2
  • 7