CODEWITHSUNDEEP
mysqlsql
Vladimir Lenkov
Vladimir Lenkov

Reputation: 73

MySQL error 1093. how to delete from this

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

Answers (2)

ABDULLAH SATTI
ABDULLAH SATTI

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

Dhaval
Dhaval

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

Related Questions