MySQL Error 1093 - Can't specify target table for update in FROM clause

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 executing:

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

How can I overcome this?

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.

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.

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) :

<pre><code>
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);
</code></pre>

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

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

Recently i had to update records in the same table i did it like below:

UPDATE skills AS s, (SELECT id FROM skills WHERE type = 'Programming') AS p
SET s.type = 'Development'
WHERE s.id = p.id;

According to [ the Mysql UPDATE Syntax][1] linked by @CheekySoft, it says right at the bottom.

> Currently, you cannot update a table and select from the same table in a subquery.

I guess you are deleting from store_category while still selecting from it in the union.


[1]:

[To see links please register here]

Try to save result of Select statement in separate variable and then use that for delete query.

*Update: This answer covers the general error classification. For a more specific answer about how to best handle the OP's exact query, please see other answers to this question*

In MySQL, you can't modify the same table which you use in the SELECT part.
This behaviour is documented at:
[

[To see links please register here]

][2]

**Maybe you can just join the table to itself**

If the logic is simple enough to re-shape the query, lose the subquery and join the table to itself, employing appropriate selection criteria. This will cause MySQL to see the table as two different things, allowing destructive changes to go ahead.

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

**Alternatively, try nesting the subquery deeper into a from clause ...**

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.

**... but watch out for the query optimiser**

However, beware that from [MySQL 5.7.6][4] and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the `optimizer_switch` variable can be used to switch off this behaviour; although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks.

SET optimizer_switch = 'derived_merge=off';

*Thanks to [Peter V. Mørch](

[To see links please register here]

) for this advice in the comments.*


Example technique was from Baron Schwartz, [originally published at Nabble][3], paraphrased and extended here.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]

If you can't do

UPDATE table SET a=value WHERE x IN
(SELECT x FROM table WHERE condition);

because it is the same table, you can trick and do :

UPDATE table SET a=value WHERE x IN
(SELECT * FROM (SELECT x FROM table WHERE condition) as t)


[update or delete or whatever]