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
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
