CODEWITHSUNDEEP
mysqlsqlsubquerysql-delete
Pradyut Bhattacharya
Pradyut Bhattacharya

Reputation: 5748

Delete using WHERE clause from the rows of the same table in MySQL

I'm having rows in the table which I intend to delete in MySQL:

delete from image_shout 
where auto_id in 
(
  select s.auto_id 
  from image_shout s 
  left join images i on s.image_id = i.image_id
  where i.image_id is null
);

For doing this I get an error:

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

Upvotes: 0

Views: 3020

Answers (1)

juergen d
juergen d

Reputation: 204904

In Mysql you can't select from a table you are deleting from. But you can trick it with another subquery.

delete from image_shout 
where auto_id in 
(
   select * from 
   (
     select s.auto_id 
     from image_shout s 
     left join images i on s.image_id = i.image_id 
     where i.image_id is null
   ) tmp_tbl
)

Or use a join directly in the delete statement

delete s 
from image_shout s
left join images i on s.image_id = i.image_id 
where i.image_id is null

Upvotes: 4

Related Questions