SQL Delete: can't specify target table for update in FROM clause

Question

I have a table *story_category* in my database with corrupt entries. The next query returns the corrupt entries.

SELECT * FROM  story_category WHERE category_id NOT IN (
SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_id=category.id);

I tried to delete them excuting:

DELETE FROM  story_category WHERE category_id NOT IN (
SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_id=category.id);

but i get the next error:

#1093 - You can't specify target table 'story_category' for update in FROM clause

Does anyone knows how can i overcome this?

Answer 1

In MySQL, you can't modify the same table which you use in the SELECT part.
This behaviour is documented at: http://dev.mysql.com/doc/mysql/en/UPDATE.html

You will need to stop using the nested subquery and execute the operation in two parts, or alternatively use a simple where clause.

Below is from Baron Schwartz, published at Nabble:

However, you can do multi-table updates like this:

UPDATE tbl AS a
  INNER JOIN tbl AS b ON ....
  SET a.col = b.col

If you absolutely need the subquery, there's a workaround, but it's ugly for several reasons, including performance:

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

The nested subquery in the FROM clause creates an implicit temporary table, so it doesn't count as the same table you're updating.

Answer 2

You could insert the desired rows' ids into a temp table and then delete all the rows that are found in that table.

which may be what @Cheekysoft meant by doing it in two steps.

Answer 3

Here is a workaround

http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/

Answer 4

If something does not work, when coming thru the front-door, then take the back-door:

drop table if exists apples;
create table if not exists apples(variety char(10) primary key, price int);

insert into apples values('fuji', 5), ('gala', 6);

drop table if exists apples_new;
create table if not exists apples_new like apples;
insert into apples_new select * from apples;

update apples_new
    set price = (select price from apples where variety = 'gala')
    where variety = 'fuji';
rename table apples to apples_orig;
rename table apples_new to apples;
drop table apples_orig;

It's fast. The bigger the data, the better.

Answer 5

The inner join in your subquery is unnecessary. It looks like you want to delete the entries in story_category where the category_id is not in the category table.

Instead of this:

DELETE FROM  story_category WHERE category_id NOT IN (SELECT DISTINCT

category.id FROM category INNER JOIN story_category ON category_id=category.id);

Do this:

DELETE FROM  story_category WHERE category_id NOT IN (SELECT DISTINCT

category.id FROM category);

Answer 6

whenever you can go round a join, then do so... even if you sometimes need to DEnormalize your schema, then do so...

Answer 7

This is what I did for updating a Priority column value by 1 if it is >=1 in a table and in its WHERE clause using a subquery on same table to make sure that at least one row contains Priority=1 (because that was the condition to be checked while performing update) :

UPDATE My_Table
SET Priority=Priority + 1
WHERE Priority >= 1
AND (SELECT TRUE FROM (SELECT * FROM My_Table WHERE Priority=1 LIMIT 1) as t);

I know it's a bit ugly but it does works fine.