You can t specify target table for update in FROM clause

Question

I have a simple mysql table   CREATE TABLE IF NOT EXISTS  pers       persID  int 11  NOT NULL AUTO INCREMENT     name  varchar 35  NOT NULL     gehalt  int 11  NOT NULL     chefID  int 11  DEFAULT NULL    PRIMARY KEY   persID     ENGINE MyISAM  DEFAULT CHARSET latin1 AUTO INCREMENT 4    INSERT INTO  pers    persID    name    gehalt    chefID   VALUES  1   blb   1000  3    2   as   1000  3    3   chef   1040  NULL     I tried to run following update  but I get only the error 1093     UPDATE pers P  SET P gehalt   P gehalt   1 05  WHERE  P chefID IS NOT NULL  OR gehalt  lt    SELECT       SELECT MAX gehalt   1 05       FROM pers MA      WHERE MA chefID   MA chefID       AS  pers      I searched for the error and found from mysql following page http   dev mysql com doc refman 5 1 en subquery-restrictions html  but it doesn t help me   What shall I do to correct the sql query

User · Answer

You can make this in three steps   CREATE TABLE test2 AS SELECT PersId  FROM pers p WHERE     chefID IS NOT NULL    OR gehalt  lt        SELECT MAX         gehalt   1 05           FROM pers MA     WHERE MA chefID   p chefID              UPDATE pers P SET P gehalt   P gehalt   1 05 WHERE PersId IN     SELECT PersId   FROM test2   DROP TABLE test2    or  UPDATE Pers P      SELECT PersId   FROM pers p   WHERE      chefID IS NOT NULL     OR gehalt  lt         SELECT MAX          gehalt   1 05             FROM pers MA      WHERE MA chefID   p chefID           t SET P gehalt   P gehalt   1 05 WHERE p PersId   t PersId

User · Answer

It s quite simple  For example  instead of writing   INSERT INTO x  id  parent id  code  VALUES       NULL       SELECT id FROM x WHERE code  AAA         BBB       you should write  INSERT INTO x  id  parent id  code  VALUES       NULL       SELECT t id FROM  SELECT id  code FROM x  t WHERE t code  AAA         BBB       or similar

User · Answer

MySQL doesn t allow selecting from a table and update in the same table at the same time  But there is always a workaround     This doesn t work       UPDATE table1 SET col1    SELECT MAX col1  from table1  WHERE col1 IS NULL    But this works       UPDATE table1 SET col1    SELECT MAX col1  FROM  SELECT   FROM table1  AS table1 new  WHERE col1 IS NULL

User · Answer

The Approach posted by BlueRaja is slow I modified it as  I was using to delete duplicates from the table  In case it helps anyone with large tables Original Query  delete from table where id not in  select min id  from table group by field 2    This is taking more time   DELETE FROM table where ID NOT IN    SELECT MIN t Id  from  select Id field2 from table  AS t GROUP BY field2    Faster Solution  DELETE FROM table where ID NOT IN     SELECT x Id from  SELECT MIN Id  as Id from table GROUP BY field2  AS t

User · Answer

MariaDB has lifted this starting from 10 3 x  both for DELETE and UPDATE       UPDATE - Statements With the Same Source and Target      From MariaDB 10 3 2  UPDATE statements may have the same source and target       Until MariaDB 10 3 1  the following UPDATE statement would not work   UPDATE t1 SET c1 c1 1 WHERE c2  SELECT MAX c2  FROM t1     ERROR 1093  HY000   Table  t1  is specified twice     both as a target for  UPDATE  and as a separate source for data       From MariaDB 10 3 2  the statement executes successfully   UPDATE t1 SET c1 c1 1 WHERE c2  SELECT MAX c2  FROM t1           DELETE - Same Source and Target Table      Until MariaDB 10 3 1  deleting from a table with the same source and target was not possible  From MariaDB 10 3 1  this is now possible  For example   DELETE FROM t1 WHERE c1 IN  SELECT b c1 FROM t1 b WHERE b c2 0      DBFiddle MariaDB 10 2 - Error  DBFiddle MariaDB 10 3 - Success

User · Answer

Make a temporary table  tempP  from a subquery  UPDATE pers P  SET P gehalt   P gehalt   1 05  WHERE P persID IN       SELECT tempP tempId     FROM           SELECT persID as tempId         FROM pers P         WHERE             P chefID IS NOT NULL OR gehalt  lt                    SELECT                       SELECT MAX gehalt   1 05                       FROM pers MA                      WHERE MA chefID   MA chefID                       AS  pers                         AS tempP     I ve introduced a separate name  alias  and give a new name to  persID  column for temporary table

User · Answer

The problem is that MySQL  for whatever inane reason  doesn t allow you to write queries like this   UPDATE myTable SET myTable A         SELECT B     FROM myTable     INNER JOIN         That is  if you re doing an UPDATE INSERT DELETE on a table  you can t reference that table in an inner query  you can however reference a field from that outer table        The solution is to replace the instance of myTable in the sub-query with  SELECT   FROM myTable   like this  UPDATE myTable SET myTable A         SELECT B     FROM  SELECT   FROM myTable  AS something     INNER JOIN         This apparently causes the necessary fields to be implicitly copied into a temporary table  so it s allowed   I found this solution here   A note from that article      You don   t want to just SELECT   FROM table in the subquery in real life  I just wanted to keep the examples simple  In reality  you should only be selecting the columns you need in that innermost query  and adding a good WHERE clause to limit the results  too

User · Answer

In Mysql  you can not update one table by subquery the same table   You can separate the query in two parts  or do    UPDATE TABLE A AS A  INNER JOIN TABLE A AS B ON A field1   B field1  SET field2

User · Answer

Just as reference  you can also use Mysql Variables to save temporary results  e g    SET  v1     SELECT        UPDATE     SET     WHERE x  v1    https   dev mysql com doc refman 5 7 en user-variables html

User · Answer

If you are trying to read fieldA from tableA and save it on fieldB on the same table  when fieldc   fieldd you might want consider this   UPDATE tableA      tableA AS tableA 1  SET      tableA fieldB  tableA 1 filedA WHERE        tableA conditionFild     condition           AND   tableA fieldc    tableA 1 fieldd      Above code copies the value from fieldA to fieldB when condition-field met your condition  this also works in ADO  e g access    source  tried myself

User · Answer

Other workarounds include using SELECT DISTINCT or LIMIT in the subquery  although these are not as explicit in their effect on materialization  this worked for me  as mentioned in MySql Doc

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

Examples related to sql

Examples related to mysql

Examples related to mysql-error-1093