Reputation: 73
Select worked:
select * from A
where A.id not in
(
select id from A as a
inner join B as b
where (a.`name` = b.`name`
and a.`status` = b.`description`))
delete doesn't work:
delete from pre_log
where pre_log.id not in
(
select id from pre_log as p
inner join sobg_table as s
where (p.`name` = s.`name`
and p.`status` = s.`description`)
)
Error Code: 1093. You can't specify target table 'pre_log' for update in FROM clause 0,001 sec
Upvotes: 2
Views: 3171
Reputation: 1
delete c.* from cars c
where c.id in (
select c2.id
from cars c1
join cars c2 on c1.model = c2.model and c1.brand = c2.brand
where c1.id < c2.id
)
this method didn't work it gave an error but then I used a second subquery in the 'where' clause to get the results of the first subquery and it worked
where c.id in (select id from (
select c2.id
from cars c1
join cars c2 on c1.model = c2.model and c1.brand = c2.brand
where c1.id < c2.id
) temp
) ```
Upvotes: 0
Reputation: 2379
You have specified pre_log table in your sub query from which you actually Delete the records
try this might help you..
delete l.* from pre_log l
where l.id not in
(
select id from
(
select id from pre_log as p
inner join sobg_table as s
where (p.`name` = s.`name`
and p.`status` = s.`description`)
) x
)
Upvotes: 4