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 < (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?

Possible duplicate of MySQL Error 1093 - Can't specify target table for update in FROM clause — Steve Chambers
– Steve Chambers, Commented Mar 16, 2017 at 13:12

BlueRaja - Danny Pflughoeft · Accepted Answer · 2017-02-16 16:04:01Z

922

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.

edited Feb 16, 2017 at 16:04

answered Jan 13, 2013 at 10:45

BlueRaja - Danny Pflughoeft

86.2k36 gold badges208 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

24 Comments

siride Over a year ago

I don't think the reason is inane. Think about the semantics. Either MySQL has to keep a copy of the table before the update started, or the inner query might use data that has already been updated by the query as it's in progress. Neither of these side-effects is necessarily desirable, so the safest bet is to force you to specify what will happen using an extra table.

BlueRaja - Danny Pflughoeft Over a year ago

@siride: Other databases, such as MSSQL or Oracle, don't have this arbitrary restriction

siride Over a year ago

@BlueRaja-DannyPflughoeft: it's not arbitrary. It's reasonable design decision based on the costs of the alternatives. The other DB systems chose to deal with those costs anyway. But those systems don't, e.g., let you include non-aggregated columns in SELECT lists when you use GROUP BY, and MySQL does. I'd argue that MySQL is in the wrong here, and I might say the same of the other DBMSs for UPDATE statements.

Tobia Over a year ago

@siride From a relational algebra point of view, T and (SELECT * FROM T) are completely equivalent. They are the same relation. Therefore this is an arbitrary, inane restriction. More specifically, it's a workaround to coerce MySQL into doing something that it clearly can do, but for some reason it cannot parse in its simpler form.

CodeX Over a year ago

In my case the accepted solution didn't work because my table was simply too large. The query never completed. Apparently this is taking too many internal resources. Instead I created a View with the inner query and used it for the data selection, which worked absolutely fine. DELETE FROM t WHERE tableID NOT IN (SELECT viewID FROM t_view); Also I recommend running OPTIMIZE TABLE t; afterwards to reduce the size of the table.

|

JJD · Accepted Answer · 2013-08-10 16:14:44Z

53

You can make this in three steps:

CREATE TABLE test2 AS SELECT PersId FROM pers p WHERE ( chefID IS NOT NULL OR gehalt < ( 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 < ( 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

edited Aug 10, 2013 at 16:14

JJD

52.7k63 gold badges218 silver badges352 bronze badges

answered Dec 13, 2010 at 13:47

Michael Pakhantsov

25.5k7 gold badges63 silver badges60 bronze badges

7 Comments

Konerak Over a year ago

Well yeah, most subqueries can be rewritten as multiple steps with CREATE TABLE statements - I hope the author was aware of this. However, is this the only solution? Or can the query be rewritten with subqueries or joins? And why (not) do that?

ubiquibacon Over a year ago

I think you have a capitalization error in your second solution. Shouldn't UPDATE Pers P read UPDATE pers P?

Alex Over a year ago

Tried this solution and for a large number of entries in temporary/second table the query can be very slow; try to create temporary/second table with an index/primary key [see dev.mysql.com/doc/refman/5.1/en/create-table-select.html ]

ShatyUT Over a year ago

As @Konerak states, this isn't really the best answer. The answer from BlueRaja below seems best to me. Upvotes seem to agree.

Pacerier Over a year ago

@Konerak, Doesn't CREATE TABLE AS SELECT give horrible performance?

|

Budda · Accepted Answer · 2013-03-09 20:42:56Z

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 < (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

Why not select the values into variables instead of doing inner inner inner selects?
SELECT ( SELECT MAX(gehalt * 1.05).. - the first SELECT does not select any column.

Yuantao · Accepted Answer · 2013-12-31 02:42:20Z

28

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 = ?

edited Dec 31, 2013 at 2:42

answered Mar 9, 2012 at 21:40

Yuantao

2,9001 gold badge20 silver badges19 bronze badges

4 Comments

Anthony Over a year ago

@grisson Thanks for the clarification. Now I get why my IN clause doesn't work - I was targeting the same table.

BlueRaja - Danny Pflughoeft Over a year ago

...this doesn't seem to actually work. It's still giving me the same error.

natbro Over a year ago

this answer actually does the more correct and efficient thing, which is using AS B on the second reference to TABLE_A. the answer in the most-upvoted example could be simplified using AS T instead of the potentially inefficient FROM (SELECT * FROM myTable) AS something, which fortunately the query optimizer typically eliminates but might not always do so.

Charles Wood Over a year ago

Can this be done for DELETE as well?

DarkSide · Accepted Answer · 2015-02-26 15:26:20Z

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.

u01jmg3 · Accepted Answer · 2021-12-02 00:28:57Z

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 t.Id FROM (SELECT MIN(Id) AS Id FROM table GROUP BY field2) AS t)

Clever! Putting the aggregate function (and GROUP BY) in the innermost subquery instead of the outermost. Thanks!

Hari Das · Accepted Answer · 2020-06-15 04:44:52Z

17

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;

answered Jun 15, 2020 at 4:44

Hari Das

11k8 gold badges69 silver badges67 bronze badges

3 Comments

Abraham Putra Prakasa Over a year ago

Thanks. I think most people confused with local env xampp using MariaDB then production server still use MySQL

Shwet Over a year ago

After finding issues for 5 hours this is answer did the work. Thank you

Rivenfall Over a year ago

good solution if you have to do it in one query but usually there's no risk in doing it in multiple queries

Filippo Mazza · Accepted Answer · 2017-07-06 08:16:31Z

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

this is good to know in general, but it doesn't work for updating/deleting multiple rows ERROR 1242 (21000): Subquery returns more than 1 row

Lukasz Szozda · Accepted Answer · 2018-08-23 17:40:22Z

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

Krish · Accepted Answer · 2013-05-08 09:37:44Z

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

Arrow · Accepted Answer · 2019-08-26 06:51:00Z

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

Tarit Mahato · Accepted Answer · 2024-01-22 11:33:08Z

you can use this query

DELETE FROM test WHERE (column_1, column_2, column_3) IN ( SELECT column_1, column_2, column_3 FROM test GROUP BY column_1, column_2, column_3 HAVING COUNT(*) > 1 );

enter image description here

to

enter image description here

Collectives™ on Stack Overflow

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

12 Answers 12

24 Comments

7 Comments

2 Comments

4 Comments

Comments

1 Comment

3 Comments

1 Comment

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

24 Comments

7 Comments

2 Comments

4 Comments

Comments

1 Comment

3 Comments

1 Comment

Comments

Comments

Comments

Comments

Linked

Related