MySQL Error 1093 - 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 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

User · Answer

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

User · Answer

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

User · Answer

As far as concerns  you want to delete rows in story category that do not exist in category   Here is your original query to identify the rows to delete   SELECT    FROM  story category  WHERE category id NOT IN       SELECT DISTINCT category id      FROM category INNER JOIN         story category ON category id category id      Combining NOT IN with a subquery that JOINs the original table seems unecessarily convoluted  This can  be expressed in a more straight-forward manner with not exists and a correlated subquery   select sc   from story category sc where not exists  select 1 from category c where c id   sc category id     Now it is easy to turn this to a delete statement   delete from story category where not exists  select 1 from category c where c id   story category category id         This quer would run on any MySQL version  as well as in most other databases that I know   Demo on DB Fiddle   -- set-up create table story category category id int   create table category  id int   insert into story category values  1    2    3    4    5   insert into category values  4    5    6    7    -- your original query to identify offending rows SELECT    FROM  story category  WHERE category id NOT IN       SELECT DISTINCT category id      FROM category INNER JOIN         story category ON category id category id        category id     ----------                1               2               3     -- a functionally-equivalent  simpler query for this select sc   from story category sc where not exists  select 1 from category c where c id   sc category id       category id     ----------                1               2               3     -- the delete query delete from story category where not exists  select 1 from category c where c id   story category category id    -- outcome select   from story category       category id     ----------                4               5

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

try this  DELETE FROM story category  WHERE category id NOT IN   SELECT DISTINCT category id  FROM  SELECT   FROM STORY CATEGORY  sc

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    Note  DELETE s restricts delete operations to the story category table Documentation  DELETE s  FROM story category s  LEFT JOIN category c  ON c id s category id  WHERE c id IS NULL

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to subquery

Examples related to sql-delete

Examples related to mysql-error-1093