Error Code: 1093. You can't specify target table 'tbl1' for update in FROM clause(Delete query)

Question

Trying to delete records with some conditions but MySQL throwing error: Error Code: 1093

delete from tbl1
where date(cast(traptime as datetime))< date_sub(current_date, interval 200  day) and 
id not in (select max(id) as id from tbl1 group by device_name);

score 1 · Accepted Answer · answered Jan 08 '16 at 12:16

1

Try this:

DELETE FROM tbl1
WHERE id NOT IN (
    SELECT id FROM (
        select max(id) as id from tbl1 group by device_name
    ) AS c
)
and date(cast(traptime as datetime))< date_sub(current_date, interval 200  day)

answered Jan 08 '16 at 12:16

Rahul Tripathi

168,305
31
280
331

1

Work Fine and thanks for super fast answer :) – Abhishek Ginani Jan 08 '16 at 12:21
@Code-Monk:- You are welcome! – Rahul Tripathi Jan 08 '16 at 12:22

score 0 · Answer 2 · edited May 23 '17 at 12:15

0

In MySQL, you can't modify the same table which you use in the SELECT part. See answer here: MySQL Error 1093 - Can't specify target table for update in FROM clause

edited May 23 '17 at 12:15

Community

1
1

answered Jan 08 '16 at 12:16

user3162020

42
5

If the answer is in another question, just flag it as a duplicate, don't post a link-only answer. – Barmar Jan 08 '16 at 13:10

Error Code: 1093. You can't specify target table 'tbl1' for update in FROM clause(Delete query)

2 Answers2