You can't specify target table 'mytable' for update in FROM clause

blackhorse

I was using subquery in a delete sql. like this

DELETE FROM mytable WHERE id IN (SELECT ... FROM mytable);

First, my subquery return some ids, and then I run delete sql to delete these records from a table with these ids.

Due to my subquery also select from the table that I run the delete sql.

I got this error message.

You can't specify target table 'mytable' for update in FROM clause.

I can always run the select query first and then run the delete query with the select query's result.

But is there a simple mysql subquery solution here?

Thanks a lot.

Weedpacket

Presumably the "..." elides some criteria for filtering which ids to select.
Why not just put those criteria directly into the WHERE clause of the DELETE statement?

blackhorse

Sorry, my mistake. I was trying to simplify the question but instead, I was misleading.

The query was

DELETE FROM mytable WHERE id IN (SELECT mytable.id FROM mytable left join mytable1 ON ...);

Weedpacket

...

blackhorse

DELETE FROM tb1 WHERE my_id IN (SELECT tb1.my_id FROM tb1 LEFT JOIN tb1_old ON ((tb1_old.active=1) AND (tb1.my_id = tb1_old.my_id) AND (tb1.name = tb1_old.name) AND (NULLIF (tb1.description, tb1_old.description) IS NULL AND NULLIF (tb1_old.description, tb1.description) IS NULL) AND (NULLIF (tb1.location, tb1_old.location) IS NULL AND NULLIF (tb1_old.location, tb1.location) IS NULL) AND (NULLIF (tb1.website, tb1_old.website) IS NULL AND NULLIF (tb1_old.website, tb1.website) IS NULL) AND (NULLIF (tb1.capacity, tb1_old.capacity) IS NULL AND NULLIF (tb1_old.capacity, tb1.capacity) IS NULL) AND (NULLIF (tb1.image, tb1_old.image) IS NULL AND NULLIF (tb1_old.image, tb1.image) IS NULL)) WHERE tb1_old.my_id IS NULL)

blackhorse

For the subquery, I think I should not use "left join" and "is null" to compare my two tables in the above case.

I should just use the row subqueries

http://dev.mysql.com/doc/refman/4.1/en/row-subqueries.html

SELECT column1,column2,column3
FROM t1
WHERE (column1,column2,column3) IN
(SELECT column1,column2,column3 FROM t2);