By Sergio del Amo


2008-09-05 10:03:46 8 Comments

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?

15 comments

@Doin 2019-07-10 20:16:10

For the specific query the OP is trying to achieve, the ideal and most efficient way to do this is NOT to use a subquery at all. Here are the LEFT JOIN versions of the OP's two queries:

SELECT s.* FROM story_category s LEFT JOIN category c ON c.id=s.category_id WHERE c.id IS NULL;

DELETE s FROM story_category s LEFT JOIN category c ON c.id=s.category_id WHERE c.id IS NULL;

@Ekonoval 2012-03-23 17:23:55

NexusRex provided a very good solution for deleting with join from the same table.

If you do this:

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

you are going to get an error.

But if you wrap the condition in one more select:

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
)

it would do the right thing!!

Explanation: The query optimizer does a derived merge optimization for the first query (which causes it to fail with the error), but the second query doesn't qualify for the derived merge optimization. Hence the optimizer is forced to execute the subquery first.

@Tyler V. 2014-09-05 23:13:19

Maybe it's because I'm in a bind today, but this was the easiest answer, even if it's maybe not the "best" one.

@Flat Cat 2014-12-14 16:40:45

This worked great, thanks! So what's the logic here? If it's nested one more level then it will be executed before the outer part? And if it's not nested then mySQL tries to run it after the delete has a lock on the table?

@Cerin 2015-12-16 17:01:30

This error and solution make no logical sense...but it works. Sometimes I wonder what drugs the MySQL devs are on...

@FastTrack 2017-06-28 15:44:20

Agree with @Cerin.. this is completely absurd, but it works.

@deFreitas 2017-07-24 16:59:43

@ekonoval Thanks you for the solution but It does not make the minimum sense for me, looks like you is fooling MySQL and he accept it, lol

@deFreitas 2017-07-24 17:02:42

@Cerin the worst thing is that it's also happen at oracle, lol

@Ekonoval 2017-07-24 17:07:06

well, I just consider it as some kind of closure, so it barely makes some sense for me =)

@ADTC 2017-12-29 19:16:33

The query optimizer does a derived merge optimization for the first query (which causes it to fail with the error), but the second query doesn't qualify for the derived merge optimization hence the optimizer is forced to execute the subquery first. Neat solution!

@user10089632 2018-01-21 07:42:18

I don't understand why people tend to over-complicate simple things, look at DanDarc's anwer

@Toni Bünter 2018-07-07 09:17:46

Thank, great solution!

@Sameer Khanal 2018-01-23 01:14:45

try this

DELETE FROM story_category 
WHERE category_id NOT IN (
SELECT DISTINCT category.id 
FROM (SELECT * FROM STORY_CATEGORY) sc;

@S.Roshanth 2018-01-11 02:14:39

The simplest way to do this is use a table alias when you are referring parent query table inside the sub query.

Example :

insert into xxx_tab (trans_id) values ((select max(trans_id)+1 from xxx_tab));

Change it to:

insert into xxx_tab (trans_id) values ((select max(P.trans_id)+1 from xxx_tab P));

@Melvin Angelo Jabonillo 2017-05-11 01:55:03

how about this query hope it helps

DELETE FROM story_category LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.id IS NULL

@Istiaque Ahmed 2017-11-09 10:19:57

The result shows : ' #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN (SELECT categories.id FROM categories) cat ON story_category.id = cat.' at line 1 '

@Sequoya 2017-04-25 11:59:56

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]

@Cheekysoft 2008-09-05 10:07:48

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: http://dev.mysql.com/doc/refman/5.6/en/update.html

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 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 for this advice in the comments.

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

@HMR 2012-12-12 03:36:56

Upvoted this answer because I had to delete items and could not get info from another table, had to subquery from same table. Since this is what pops up on top while googling for the error I got this would be the best fit answer for me and a lot of people trying to update while subquerieing from the same table.

@Pacerier 2015-02-24 03:47:27

@Cheekysoft, Why not save the values into variables instead?

@Peter V. Mørch 2015-11-06 15:36:41

Beware, that from MySQL 5.7.6 on, the optimizer may optimize the sub-query away and still give you the error, unless you SET optimizer_switch = 'derived_merge=off'; :-(

@user2180613 2016-08-18 18:59:04

@PeterV.Mørch Following mysqlserverteam.com/derived-tables-in-mysql-5-7, in case certain operations are carried out, merging cannot happen. E.g. provide the derived dummy table with a LIMIT (to inifity) and the error will never occur. That's pretty hackish though and there is still the risk that future versions of MySQL will support merging queries with LIMIT after all.

@Rodolphe 2017-01-17 09:30:10

The INNER JOIN update works well ! Awesome trick

@JoelBonetR 2018-09-10 09:56:33

Can you, please, provide a full example about this workaround? UPDATE tbl SET col = ( SELECT ... FROM (SELECT.... FROM) AS x); i'm still getting errors

@lipika chakraborty 2016-05-31 11:35:18

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

@DanDarc 2009-06-03 21:40:18

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

Do this:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category);

Instead of that:

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

@hoyhoy 2010-09-07 17:35:05

This should be the top answer! Maybe delete the first "instead of".

@shA.t 2015-05-31 09:38:51

I think DISTINCT is unnecessary here - for a better performance ;).

@Jeff Lowery 2017-03-24 18:03:20

I must be crazy. The answer is the same as what was stated in the original.

@Richard 2017-11-28 07:26:48

This is the answer for me also. Don't where in on the ID column, so you dont need to subquery the primary table.

@ToolmakerSteve 2019-04-19 12:45:47

@JeffLowery - the first code block is the answer here; its the second code block that is from the question.

@briankip 2015-01-08 13:18:46

According to the Mysql UPDATE Syntax 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.

@Pratik Khadloya 2012-10-04 23:39:44

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;

@lilbyrdie 2014-05-30 15:23:10

Can't this just be written as UPDATE skills SET type='Development' WHERE type='Programming'; ? This doesn't seem to be answering the original question.

@shadyyx 2014-12-05 10:00:57

Seems like overkill, @lilbyrdie is correct - it could only be UPDATE skills SET type='Development' WHERE type='Programming';. I do not understand why so many people is not thinking about what they do...

@Steffen Winkler 2015-09-07 14:28:16

this is the best answer here, IMHO. It's syntax is easy to understand, you can re-use your previous statement and it's not limited to some super specific case.

@Ligemer 2016-07-18 22:19:17

Temporary table worked like a charm for me. Nice.

@AnrDaemon 2017-04-26 13:11:04

Even if the example case is questionable, the principle is the best to understand and deploy in real-world scenarios. The shown query works because implicit join in table list creates a temporary table for a subquery, thus providing stable results and avoiding clash between retrieval and modification of the same table.

@smac89 2018-01-09 23:40:03

regardless of what anyone might say about this being overkill, it still answers the question in terms of the title. The error which the OP mentioned is also encountered when a update is attempted using the same table in a nested query

@Marco S. 2018-07-21 11:52:27

@AnrDaemon, very usefull features. What about optimization in accessing the temporary table created ? Does it still use the indexes of the original table ?

@learner 2019-02-16 17:19:15

@lilbyrdie, Doing as you have shown in your example will throw Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. And it's the best approach I found without Inner Join. Thank you Pratik.

@NexusRex 2011-12-23 20:03:08

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
)

@Akshay Arora 2016-01-27 13:00:11

Can you explain why this works, why just nesting one more level works? This question has been asked already as a comment to @EkoNoval's question but nobody responded. Maybe you can help.

@Istiaque Ahmed 2017-11-09 10:00:31

@AkshayArora, Go through the part under the heading 'Maybe you can just join the table to itself' in @Cheekysoft's answer. He said UPDATE tbl AS a INNER JOIN tbl AS b ON .... SET a.col = b.col - this would work as a different alias for the same table is used here . Similarly in @NexusRex's answer the first SELECT query acts as a derived table into which story_category is used for the second time. So the error mentioned in the OP should not take place here, right ?

@sactiw 2010-09-02 07:31:45

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.

@sactiw 2010-12-20 16:42:17

@anonymous_reviewer: In case of giving [-1] or even [+1] to someone's comment please also mention why have you given it. Thanks!!!

@Chris 2011-01-27 20:10:12

-1 because this is incorrect. You can't modify the same table that you use in the SELECT statement.

@sactiw 2011-02-18 15:03:08

@Chris I have verified it on MySQL and it works just fine for me so I would request you to please verify it at your end and then claim it to be correct or incorrect. Thanks!!!

@Chris 2011-02-19 00:08:28

At the bottom of this page it says 'Currently, you cannot update a table and select from the same table in a subquery.' - and I have experienced this to be true on many occasions. dev.mysql.com/doc/refman/5.0/en/update.html

@sactiw 2011-02-21 13:39:30

@Chris I know that, but there is a workaround for that and which is exactly what I have tried to show with my 'UPDATE' query and believe me it works just fine. I don't think you have really tried to verify my query at all.

@Jeff Davis 2012-12-20 14:58:26

This works fine. It's the same general structure as the accepted answer.

@Thiago Macedo 2014-05-20 19:24:32

Unique answer here I used to get working in the case of the select returning multiple items and the where matches a IN. But, in fact, @NexuxRex is the complete example for my case.

@Mhluzi Bhaka 2016-11-02 01:28:55

Works fine for my needs - seems a good way to bypass the error in question.

@user2342558 2019-09-23 07:39:17

It works very well! It's a so simple solution! Thanks

@Tom Schaefer 2009-03-29 02:26:30

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.

@Walf 2014-12-09 06:48:49

And you've just lost all your foreign keys, and maybe you have some cascading deletes, too.

@YonahW 2008-09-05 10:25:35

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.

Related Questions

Sponsored Content

11 Answered Questions

[SOLVED] You can't specify target table for update in FROM clause

11 Answered Questions

[SOLVED] Insert into a MySQL table or update if exists

2 Answered Questions

6 Answered Questions

1 Answered Questions

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

  • 2016-02-09 11:39:55
  • Khalil Enn
  • 210 View
  • 2 Score
  • 1 Answer
  • Tags:   mysql duplicates

2 Answered Questions

[SOLVED] Can't specify target table for update, delete query in mysql

2 Answered Questions

2 Answered Questions

[SOLVED] MySQL Error You can't specify target table for update in FROM clause

Sponsored Content