How to select from an update target in MySQL at Xaprb

How to select from an update target in MySQL

Published at June 23, 2006 in SQL. Tags: No Tags.

MySQL doesn’t allow referring to a table that’s targeted for update in a FROM clause, which can be frustrating. There’s a better way than creating endless temporary tables, though. This article explains how to update a table while selecting from it in a subquery.

The problem

Suppose I want to update a table with data from a subquery that refers to the same table. I might want to do this for a variety of reasons, such as trying to populate a table with its own aggregate data (this would require assignment from a grouped subquery), updating one row from another row’s data without using non-standard syntax, and so on. Here’s a contrived example:

create table apples(variety char(10) primary key, price int);

insert into apples values('fuji', 5), ('gala', 6);

update apples
    set price = (select price from apples where variety = 'gala')
    where variety = 'fuji';

The error message is ERROR 1093 (HY000): You can't specify target table 'apples' for update in FROM clause. The MySQL manual mentions this at the bottom of the UPDATE documentation: “Currently, you cannot update a table and select from the same table in a subquery.”

It’s pretty easy to work around the problem in this contrived example, but there are times when it’s not possible to write the query without subqueries that refer to the update target. There is a workaround, though.

The workaround

Since MySQL materializes subqueries in the FROM clause (“derived tables”) as temporary tables, wrapping the subquery into another inner subquery in the FROM clause causes it to be executed and stored into a temporary table, then referenced implicitly in the outer subquery. The following statement will succeed:

update apples
   set price = (
      select price from (
         select * from apples
      ) as x
      where variety = 'gala')
   where variety = 'fuji';

If you want to know more about how this works, read the relevant section in the MySQL Internals Manual.

Problems this trick doesn’t avoid

One common frustration this doesn’t solve is the issue of badly optimized queries in the IN() clause, which are rewritten as correlated subqueries, sometimes (usually?) causing terrible performance. Wrapping the subquery in another subquery doesn’t prevent the optimizer from rewriting it as a correlated subquery, though, unless I go to extremes. In any case it’s better to just rewrite such a query as a join.

Another thing it won’t do is allow a query to refer to a temporary table more than once. Neither of these issues is solvable with the “wrap it in a subquery” trick because they are created at query compile time, whereas the update issue I was able to solve above happens at query run time.

If you enjoyed this article, subscribe via feeds or e-mail to receive my articles regularly.

Update 2006-08-29 The queries I’ve given here are sloppy, performance-wise. 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.

Technorati Tags:No Tags

24 Responses to “How to select from an update target in MySQL”

Feed for this Entry Trackback Address

1 Andreas Mauf
Aug 29th, 2006 at 5:37 am

Unfortunately it does’t work in my case. I tried the following query in MySQL 4.1.11 and get the same error again:

UPDATE `phpads_banners` SET `compiledlimitation` = (
SELECT `compiledlimitation`
FROM (SELECT * FROM `phpads_banners`) AS b
WHERE `bannerid` = 33 )
WHERE `bannerid` = 34

#1093 - You can’t specify target table ‘phpads_banners’ for update in FROM clause
2 Xaprb
Aug 29th, 2006 at 8:22 am
Hmm, that’s not working for me either on 4.1.21. I’m not sure why — maybe I tested the technique in the article on 5.x. I’d guess at the reason, but I’d likely be wrong. However, here’s a workaround:
```
update phpads_banners as a
   inner join (
      select * from phpads_banners
      where bannerid = 33
   ) as b on a.bannerid=34 and b.bannerid = 33
set a.compiledlimitation = b.compiledlimitation;
```
3 Andreas Mauf
Aug 29th, 2006 at 10:52 am

Thx, this works. I think some infos about the version number tested with is always usefull ;-)

PS: Nice site. I already added it to my feedreader for coming back later…
4 appreciative
Aug 30th, 2006 at 2:16 pm

Thanks a lot for this hint. It is a lifesaver.
5 Matthias
Oct 24th, 2006 at 6:03 am

great!!

6 Fijjit

Oct 28th, 2006 at 12:05 pm

Hi,

How can I use this workaround with the following statement?

UPDATE webartCategories AS cat
   SET level=level+1
   WHERE cat.parent=111
   OR EXISTS(
      SELECT 1 FROM webartCategories AS cat1,
         webartCategories AS cat2
      WHERE (cat.parent=cat1.id AND cat1.parent=111)
         OR (cat.parent=cat1.id AND cat1.parent=cat2.id AND cat2.parent=111))

This handles items in a hierarchical tree.

Any help would be greatly appreciated. Thanks.

7 Sheridan
Oct 28th, 2006 at 11:29 pm
Workaround w/ Lots of Cols

I have 10 columns that need to be updated via a subquery to the updated table.

Your workaround could work with an additional workaround: having the inner SELECT concatenenate all the selected columns into one, followed by a query that updates the 10 columns by extracting the values from the concatenated column.

To continue the apples analogy with apples:
```
update apples
   set concated_cols = (
      select CONCAT(price,'-',quality,'-',color)  from (
         select * from apples
      ) as x
      where variety = 'gala')
   where variety = 'fuji';
```
Then, a 2nd query distibutes the entries of concatenated_cols to the appropriate “real” columns.

BUT, is there a better way?

-Chas

P.S., my kudos on this site also.
8 Xaprb
Oct 29th, 2006 at 8:30 am
Lukas, you hit one one of the major reasons RDBMS vendors provide the ability to update from a JOIN. Purists angrily say a searched update statement is the only right way to do it, but if you have to update more than one column, the purist’s way to do it looks like this:
```
update apples
   set price = (
      select price from (
         select * from apples
      ) as x
      where variety = 'gala'),
   color = (
      select color from (
         select * from apples
      ) as x
      where variety = 'gala'),
   where variety = 'fuji';
```
In other words, every value you want to update requires a subquery to search for the new value. You’re going to need some additional logic there too, such as CASE statements to avoid setting columns to NULL in case the subquery returns nothing.

Joining values into a string and then splitting them apart doesn’t work around this. Generally, if it feels like an ugly sin, it is probably not the best way. (Even the subquery technique I’m demonstrating on this page is an ugly sin, only forced upon our heads by MySQL peculiarities. Not that I’m complaining. You will find ugly hacks on every platform.)

Thanks for writing in!
9 Xaprb
Oct 29th, 2006 at 8:36 am
Hi Fijit,

Thanks for writing in. This statement is pretty complex. Let me see if I can understand it. You want to increase the row’s level if it is either a) a child of 111, b) a grandchild of 111, c) a great-grandchild of 111. In other words, if you have the following table it should update the last three rows:
```
+-----+-------+--------+
| id  | level | parent |
+-----+-------+--------+
| 111 |     1 |      0 |
| 112 |     2 |    111 |
| 113 |     3 |    112 |
| 114 |     4 |    113 |
+-----+-------+--------+
```
You can’t use the subquery trick in this case because your subquery is correlated, i.e. it refers to the outer aliased table ‘cat’ in the subquery. MySQL doesn’t allow using a subquery in the FROM clause in a correlated subquery because it builds a temporary table for it. So far so good — you know all this.

The only way I can think of is to write a query that will accept the value 111 and return every row that’s a descendent, up to three levels deep, then nest that query into a FROM clause and join against it. Here’s one possible query:
```
select cat1.id as id1, cat2.id as id2, cat3.id as id3
from webartCategories as cat1
   left outer join webartCategories as cat2 on cat2.parent = cat1.id
   left outer join webartCategories as cat3 on cat3.parent = cat2.id
where cat1.parent = 111;

+-----+------+------+
| id1 | id2  | id3  |
+-----+------+------+
| 112 |  113 |  114 |
+-----+------+------+
```
I used LEFT joins so the join would succeed even if there aren’t three levels of descendents. Now you have all three values in a single row, which isn’t really ideal: you can’t put it into an IN() clause because it wants a single column, and what if there are multiple rows in the result?. You could mess around with this and get it to work a couple different ways, depending on your schema and the results. Here’s one:
```
update webartCategories as cat
    inner join ( [copy/paste query above] ) as x on id in (id1, id2, id3)
   set level = level + 1
```
This might perform badly, depending on your schema and how much data you have. (See my articles about profiling queries for how to really find out). Here’s another way to do this:
```
select id from webartCategories where parent = 111
union all
   select cat2.id
   from webartCategories as cat1
      inner join webartCategories as cat2 on cat2.parent = cat1.id
   where cat1.parent = 111
union all
   select cat3.id
   from webartCategories as cat1
      inner join webartCategories as cat2 on cat2.parent = cat1.id
      inner join webartCategories as cat3 on cat3.parent = cat2.id
   where cat1.parent = 111

+-----+
| id  |
+-----+
| 112 |
| 113 |
| 114 |
+-----+
```
Now at least you have a single column and unlimited rows, which is a lot better to work with relationally. (Notice I’m using INNER joins now). You could join against this in a subquery:
```
update webartCategories as cat
    inner join ( [copy/paste query above] ) as x on cat.id = x.id
   set level = level + 1
```
Again, whether that performs well depends on your data.

Recursive queries suck. You’re only simulating recursion, three levels deep. The official SQL standard does have some means to write recursive queries, at least some of which are supported in MS SQL Server 2005, but MySQL doesn’t support any of them. It might be a better idea to repeatedly issue a query that updates children, then if it matched any rows update grandchildren, and so on. If you do this, at least you can auto-generate queries that recurse as deeply as needed, and your code can stop as soon as no more rows are matched. It’s a poor man’s recursive query.
10 Gowrav Vishwakarma
Mar 29th, 2007 at 8:37 am

I am using Myqsl and working on parent children database where depth is unlimited ( not fixed .. not unlimited actually) and recursion sucks… i use php for recursion .. is there any way around to make this all working through Mysql query.. means go up till sponsor/parentId <> 0 or go down till leftId <>0 or RightID<>0 or ….

help is welcome with a great thanks in advance if there any way around
11 Xaprb
Mar 29th, 2007 at 9:40 am

There are several ways to handle hierarchical data, such as nested sets etc. Joe Celko’s book about it is said to be a classic, though I have not found time to read it myself! There’s a great explanation of nested sets in Pro MySQL.
12 Anthony Goddard
May 13th, 2007 at 8:58 am

This works for me fine in MySQL 5.0.24, but not on 4.1.11. Just wanted to check that others have the same issue, or is there something I’m missing?

Cheers!
Ant.
13 Kerry
Aug 19th, 2007 at 6:29 pm

Hey,

It looks like you have the solution for my needs but I really have no idea how to implement what you’re talking about in my code… as I don’t think I fully understand it. If you could take a look at this and let me know how to do it or give me more examples so I can figure it out, that would be great :):

UPDATE rehabs SET phone2 = ( SELECT t1.phone
FROM rehabs t1, rehabs t2
WHERE t1.name = t2.name
AND t1.address1 = t2.address1
AND t1.address2 = t2.address2
AND t1.city = t2.city
AND t1.zip = t2.zip
AND t1.url = t2.url
AND t1.phone t2.phone
AND t1.rehabID t2.rehabID )
WHERE EXISTS (

SELECT t1.phone
FROM rehabs t1, rehabs t2
WHERE t1.name = t2.name
AND t1.address1 = t2.address1
AND t1.address2 = t2.address2
AND t1.city = t2.city
AND t1.zip = t2.zip
AND t1.url = t2.url
AND t1.phone t2.phone
AND t1.rehabID
14 Xaprb
Aug 20th, 2007 at 8:18 am
It looks like you can just rewrite that as a self-join instead of using subqueries. This will probably work much better:
```
update rehabs as t1
   inner join rehabs as t2 using(name, address1,...)
set t1.phone2 = t2.phone
```
15 Daniel Barradas
Nov 28th, 2007 at 5:42 am

This worked for me.

I needed to update a number with the max value of a column in the same table.

update inscricoes
set dorsal = (
select dorsal from (
select max(dorsal) 1 as dorsal from inscricoes
) as dorsal)
where id = ‘$id’

Do you see any problem that may arise from this approach? (with concurrency maybe?)

Anyway … thanks for sharing! ;)
16 Andy
Feb 14th, 2008 at 4:28 pm

This works great even for delete statements. I had a table with a linked id to another table but didn’t create a cascade foreign key yet and had some entries from the top table deleted. So to add a foreign key, I had to delete all the entries from the linked table that had nothing linking to it so I did this:

DELETE FROM tbldetails WHERE ID IN (SELECT ID FROM (SELECT tbldetails.ID FROM tbldetails LEFT JOIN tbltop ON tbltop.ID=tbldetails.topid WHERE top IS NULL) AS X);

(I replaced the actual names that would make more sense to what I’m talking about, hopefully). Although I would think this is a common thing and wonder if there’s an easier way to do that.
17 T1
Mar 29th, 2008 at 9:54 am

What’s a good book on this topic?
18 Xaprb
Mar 29th, 2008 at 11:03 am

On how to understand how MySQL works? The book I helped write, when it is published, will be the best. http://www.oreilly.com/catalog/9780596101718/ Note that that’s the second edition: the first edition is not as detailed.

Otherwise, I think Pro MySQL is a very good book, written by my friends Jay Pipes and Mike Kruckenberg. http://www.apress.com/book/view/159059505X
19 David
Apr 9th, 2008 at 6:01 pm

(^__^)
(_ _)
(^__^)
(_ _)

thank you x
20 mysticav
Apr 21st, 2008 at 12:47 am

For a credit / debit/ balance table, this is an example:

INSERT INTO account set credit = 100, balance =ifnull((select balance from (select ((sum(credit)-sum(debit))) as balance from account) as x),0) 100

For instance, If you run twice the query, the table will be:

| credit | debit | balance
100 0 100
100 0 200
21 mebugus
May 21st, 2008 at 12:24 am

Only Thanks for this hint.
22 Eyal Carmi
May 24th, 2008 at 9:18 am

Hi,
I want to raise a performance question regarding this solution and hope someone will have an answer to it.
I have a table with millions of rows, this table has a unique key: id.
For each row there are:
id, val_1,val_2, matchingRowId
I have already computed and updated the matchingRowId for each row and now i want to set each row’s ‘val_2′ based on ‘val_1′ of the mathcing row.

Originally i used:

update myTable set val_2=
(select T1.val_1
from myTable as T1
where T1.id=myTable.matchingRowId);

This of course failed but when i tried rewriting the query as you suggested to:

update myTable set val_2=
(select T1.val_1
from (select * from myTable) as T1
where T1.id=myTable.matchingRowId);

This works but the performance is terrible (since myTable is very large). I then read your second paper but i am not able to optimize the query since the following:

update myTable set val_2=
(select T1.val_1
from (select *
from myTable as T1
where T1.id=myTable.matchingRowId));

But got an error message saying that ‘myTable’ is not recognized in the inner loop.

Can anyone help?

Thanks
23 sql_developer
Aug 14th, 2008 at 6:33 pm

Thanks very much for this solution. I was having a hard time finding a portable way to do this.

1 How to select from an update target in MySQL · Style Grind
Pingback on Aug 5th, 2006 at 3:08 pm

Xaprb