How to select from an update target in MySQL at Xaprb

How to select from an update target in MySQL

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.

Further Reading:

Written by Xaprb

June 23rd, 2006 at 9:54 pm

Posted in Uncategorized

55 Responses to 'How to select from an update target in MySQL'

Subscribe to comments with RSS

[...] 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. [...]

How to select from an update target in MySQL · Style Grind

5 Aug 06 at 3:08 pm
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

Andreas Mauf

29 Aug 06 at 5:37 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;
```
Xaprb

29 Aug 06 at 8:22 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…

Andreas Mauf

29 Aug 06 at 10:52 am
Thanks a lot for this hint. It is a lifesaver.

appreciative

30 Aug 06 at 2:16 pm
great!!

Matthias

24 Oct 06 at 6:03 am

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.

Fijjit

28 Oct 06 at 12:05 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.
Sheridan

28 Oct 06 at 11:29 pm
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!
Xaprb

29 Oct 06 at 8:30 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.
Xaprb

29 Oct 06 at 8:36 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

Gowrav Vishwakarma

29 Mar 07 at 8:37 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.

Xaprb

29 Mar 07 at 9:40 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.

Anthony Goddard

13 May 07 at 8:58 am
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

Kerry

19 Aug 07 at 6:29 pm
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
```
Xaprb

20 Aug 07 at 8:18 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! ;)

Daniel Barradas

28 Nov 07 at 5:42 am
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.

Andy

14 Feb 08 at 4:28 pm
What’s a good book on this topic?

T1

29 Mar 08 at 9:54 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

Xaprb

29 Mar 08 at 11:03 am
(^__^)
(_ _)
(^__^)
(_ _)

thank you x

David

9 Apr 08 at 6:01 pm
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

mysticav

21 Apr 08 at 12:47 am
Only Thanks for this hint.

mebugus

21 May 08 at 12:24 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

Eyal Carmi

24 May 08 at 9:18 am
Thanks very much for this solution. I was having a hard time finding a portable way to do this.

sql_developer

14 Aug 08 at 6:33 pm
Hi! I’m russian. Please help me. This is query for delete duplicate entries in “blogs” table. This query not work. How to rewrite a query that he worked?

Query:

DELETE FROM blogs WHERE ID NOT IN (
SELECT ID
FROM blogs
GROUP BY DOMAIN
)

Alex

31 Aug 08 at 3:39 am
Hi Alex, see http://www.xaprb.com/blog/2005/09/23/how-to-write-a-sql-exclusion-join/

Xaprb

3 Sep 08 at 10:42 am
Hi to all

Thanks for cool disussion, it is very usefull.

I’m seeking for workaround for the following query:

UPDATE `operation` SET `status`=”302″ WHERE `status`=”301″ AND NOT EXISTS (SELECT * FROM `operation` AS `x` WHERE `file`=”365″ AND `status`=”302″)

it is a part of the batch egine, which executes operations, scheduled for the executing in the `operation` table.

in the table `operation` there are storing operations which refers to the files. we need to set status “started” (302) for “scheduled” operation (`status` = 301) in the case when file does not have yet any “started” operation.

any suggestions? thanks

ovoznyak

27 Sep 08 at 3:08 am
You need a multi-table update (look in the MySQL manual if you’re not familiar with them) and an exclusion join: http://www.xaprb.com/blog/2005/09/23/how-to-write-a-sql-exclusion-join/

Xaprb

27 Sep 08 at 5:14 pm
thx very much. helped with a difficult query like:

UPDATE t SET foo = 1
WHERE foo = 2 AND bar NOT IN (
SELECT bar FROM (
SELECT DISTINCT bar FROM t WHERE foo = 1
) tmp
)

Vitali

22 Oct 08 at 5:41 am
Remarkably useful. I used this to prune “dangling” entries from a table, that is entries that don’t actually have any link to another table:

DELETE FROM Invoices WHERE HSSA IN (SELECT * FROM (SELECT i.HSSA From Invoices i LEFT JOIN InvoiceItems ii ON i.HSSA=ii.HSSA WHERE ii.HSSA IS NULL) t);

Robert

26 Jan 09 at 4:28 pm
Thank you! I was fighting with this for quite a while. The only other solution I found was to use a BEFORE INSERT trigger, which turned into a nightmare.

Cat

6 Feb 09 at 12:46 pm
Thanks, that helped a lot.

Andrew

11 Feb 09 at 4:02 pm
Thanks much!!! works for inserts too. It’s beautiful.

Dan

5 May 09 at 4:48 pm
Very useful tips, thanks!
Although it didn’t really solve my problem (possibly due to my lack of understanding ;D).
I’m wrestling with the following difficulty:
I need to update two fields of my table with the result of two counts performed on the same table, similar to:
UPDATE table1 t SET
t.number1 = (SELECT count(0) FROM table tt
WHERE tt.id_parent IS NOT NULL
AND tt.id_parent = t.id),
t.number2 = (SELECT count(0) FROM table tt, anothertable ot
WHERE tt.id_parent IS NOT NULL
AND tt.id_parent = t.id
AND ot.id = tt.id
AND ot.state = 1);
Thanks to your blog I discovered that you ‘cannot update a table and select from the same table in a subquery’, but I fail to see the solutions to my problem.
Any help would be greatly appreciated…
Thanks in advance!!

Jan

1 Jul 09 at 7:48 am
Oops! I noticed an error slipped into the sql of my previous post; it should be “UPDATE table t SET …”

Jan

1 Jul 09 at 11:00 am
Hi

Trying to get back into SQL after too many years not doing anything technical ! Trying to achieve the following :

UPDATE CCproducts SET `description` = (SELECT `products_description` FROM `products_description`, `CCproducts` WHERE `CCproducts`.`products_parent_id` = `products_description`.`products_id`);

But obviously get the 1093 error just cant see how to get around it – can anyone help pelase ?

Thanks

Ian

6 Aug 09 at 7:32 pm
Great post. It worked like a charm and I feel empowered now too. Your book “High Performance MySQL” is fantastic! I read it cover to cover; never done that with a technical book before. I usually skip around for the info that interests me, but this book was awesome. You and your fellow authors really know your stuff.

Chris Kleeschulte

6 Oct 09 at 8:10 am
Chris, I’m very happy you like the book! Tell your friends :)

Xaprb

7 Oct 09 at 8:39 pm
Let’s take 2 queries:

A: Following this article:
UPDATE response SET status = 3 WHERE status = 1 AND title IN (SELECT title FROM (SELECT title FROM response GROUP BY title HAVING COUNT(1) >= 10) as derived_response)

B: Other solution:
UPDATE response, (SELECT title, count(1) as count from response WHERE status = 1 GROUP BY title) AS tmp SET response.status = 3 WHERE status = 1 AND response.title = tmp.title AND count >= 10;

I have the following results for the same dataset:

Variable: Value_for_A Value_for_B
———————————–
Bytes_received: 169 213
Bytes_sent: 52 52
Com_update: 1 0
Com_update_multi: 0 1
Created_tmp_tables: 1 3
Handler_read_first: 1 0
Handler_read_key: 0 3415
Handler_read_next: 10003 3414
Handler_read_rnd: 0 393
Handler_read_rnd_next: 2092544 788
Handler_update: 0 3021
Handler_write: 609 786
Key_read_requests: 746 259
Last_query_cost: 162.249 12396.994455
Questions: 3 3
Select_scan: 2 1
Sort_rows: 0 393
Sort_scan: 0 1
Table_locks_immediate: 2 2

The last_query_cost, Created_tmp_tables, etc show that the solution A (from this article) is the winner.
But the value from Handler_read_rnd_next is terrible.

Do you have any solution to improve the results?

Thanks for your feedback. :)

idont

8 Oct 09 at 6:52 pm
Is it me, or is this whole subquery thing lame on lame? I encounter situations that require this type of thing all the time:

– MySQL HACK! Update the last row inserted
update grp set is_reserved = 0
where id = (select id from (select max(id) as id from grp ) as s1)

SQL Server, Oracle, Sybase, Informix, hell even Microsoft Access allows the standard solution:

– The correct way
update grp set is_reserved = 0
where id = (select max(id) as id from grp)

Would somebody explain to me why, or point me to a thread that discusses the philosophical arguments as to why MySQL should not support what you can do with just about every other RDBMS?

Every time I get sucked into a project that uses MySQL is find two things: cool little features MySQL has that all the others should, and lots of silly things that just make me ill wishing I could retreat to a different engine (don’t get me started on trying to use timestamps for last_modified columns!).

Thanks,

Sean S

Sean

13 Nov 09 at 4:50 pm
Hello, I just found a lot of help on this thread about subquery.
But wenn I update a table with a timeline function, i have a problem.
The startdate is the enddate from the previous sentence and the enddate will be the new startdate + xminutes, so I became at last a list with no timelecks.

But (I think because the subquery is temp table??) the startdate will not be ok.

timeline before update:
OrderNr| Startdate | Enddate | xminutes
1 | 2010-02-09 12:00:00 | 2010-02-09 12:10:00 | 10
2 | NULL | NULL | 20
3 | NULL | NULL | 35

timeline perfect:
OrderNr| Startdate | Enddate | xminutes
1 | 2010-02-09 12:00:00 | 2010-02-09 12:10:00 | 10
2 | 2010-02-09 12:10:00 | 2010-02-09 12:30:00 | 20
3 | 2010-02-09 12:30:00 | 2010-02-09 13:05:00 | 35

UPDATE timeline as A
SET StartDate = ( SELECT EndDate FROM ( SELECT * FROM timeline ) AS X WHERE X.OrderNr = A.OrderNr – 1 ),
EndDate = StartDate + INTERVAL ( xminutes ) MINUTE
where OrderNr > 1;

You have an idea?
Thanks Wido

wido

10 Feb 10 at 9:13 am
[...] la misma tabla para la cláusula FROM de la subconsulta y el objetivo a actualizar. Tras leer esta historia, lo he resuelto usando una tabla [...]

MySQL: cómo copiar un dato de una fila a otra fila de la misma tabla « Historias de Cronopios

8 Mar 10 at 6:15 am
Uggh! This is UGLY! My first project where I’ve been forced to use MySQL, and it is really putting me off it. Postgres handles all this stuff perfectly. My best bit of advice here is use Postgres instead if you can, it’s SQL handling is MUCH better.

Craig

11 Oct 10 at 11:24 pm
try
“update apples
set price = (select A.price from apples A where
variety = ‘gala’)
where variety = ‘fuji’;”

It work for me

Beppe

10 Nov 10 at 10:04 am
Doesn’t work for me, still get the same problem, what mysql version are you using.

mysql> update apples set price = (select A.price from apples A where variety = ‘gala’) where variety = ‘fuji’;
ERROR 1093 (HY000): You can’t specify target table ‘apples’ for update in FROM clause

Server version: 5.1.41-3ubuntu12.6 (Ubuntu)

Craig

10 Nov 10 at 8:48 pm
Great workaround, thanks a lot.
Have you raised this issue with anyone at mysql dev yet? As you mentioned, it is something that could needs working on in future versions I think.

Marcool

26 Dec 10 at 1:19 pm
Awesome information on this. Thanx everyone. Solved my problem.

Pavan

3 Feb 11 at 12:10 am
I don’t know if it was already said, but we can do:

select price from apples as x where variety = ‘gala’

instead of:

select price from (select * from apples) as x where variety = ‘gala’

swilmet

2 Mar 11 at 6:27 am
Trying to get the following to work based on this tip, but not quite getting it.

I have a table with a unique ID, Sales Order Number, Sales Order line # and a Count column. Since every sales order can have 1 to many line items I have many rows for the same sales order number, but all have a unique ID. I want to set the Count column = for just the first (Min ID) row for each sales order.

Before:

ID Sales Order # Line # Count
—– ————- —— —–
1 100 1 0
2 200 1 0
3 200 2 0
4 200 3 0
5 300 1 0
6 300 2 0

After I want:

ID Sales Order # Line # Count
—– ————- —— —–
1 100 1 1
2 200 1 1
3 200 2 0
4 200 3 0
5 300 1 1
6 300 2 0

Note: I am not guaranteed that the line # = 1 for all the rows where I want to set Count = 1, the condition needs to be the MIN(ID) for the Sales Order is where Count needs to be set to 1.

I was able to get it to work when I supplied a single Sales Order # in my where condition, but I can;t figure out how to make it generic and link the SalesOrder from the inner Query to the outer query.

Beebo

1 Apr 11 at 12:10 pm
Thanks for sharing this workaround. It was exactly what I needed to have an accurate “default” rank for a voting site. Really appreciate you taking the time to post it!

Sarah Lewis

8 Apr 11 at 5:15 pm
Hi, I know your are going to help me out, this is the structure of the table, I need to update (ocupado, blouqeado and tamano to ’0′) from file_name AA010202 to AA010106 with the script I have it gives me an error, what can i do….????

file_id | file_name |ocupado |bloqueado |tamano
3 | AA010102 | 1 | 1 | 40
4 | AA010104 | 1 | 1 | 40
5 | AA010106 | 0 | 1 | 0
6 | AA010108 | 0 | 1 | 0
7 | AA010110 | 0 | 1 | 0
8 | AA010202 | 1 | 1 | 40
9 | AA010204 | 0 | 0 | 0

UPDATE posiciones
SET
ocupado = ’0′,
bloqueado = ’0′,
tamano = ’0′
WHERE file_name =
(
SELECT MAX(file_name)
FROM posiciones
WHERE bloqueado > ’0′
)
AND file_name =
(
SELECT MAX(file_name)
FROM posiciones
WHERE bloqueado > ’0′
AND ocupado = ’1′
AND file_name !=
(
SELECT MAX(file_name)
FROM posiciones
WHERE bloqueado > ’0′
)
)

by the way THANK YOU a lot just for rading …!

JESUS

15 Jun 11 at 3:19 pm
hey men muchas gracias

javaspc

15 Jun 11 at 6:05 pm
Hello there, I’ve created my command for oracle and now I need it for mysql. Actually I have no clue of how to do this with one update command (will be a trigger afterwords). Here is my oracle command. I would appreciate if anyone can convert this to mysql.

UPDATE animal_table
SET (animal_info1,
animal_info2,
animal_info3) = (SELECT animal_nbr, animal_amount, ’00′
FROM animal_table t2
WHERE t2.category_id = animal_table.category_id
AND t2.sumary_id = animal_table.summary_id
AND t2.animal_type = ‘special’
)
WHERE animal_type = ‘cats’
AND category_id = ‘foo’

Maglebolia

27 Sep 11 at 10:26 am
You need to make the inner select a temporary table (just wrap the select in another select).

Here is a good example.

http://www.toofishes.net/blog/agony-simple-things-mysql/

Annoying and painful, but that’s mysql..

Postgres would run the same sql as oracle with no problems, it’s a much better enterprise DB in my opinion.

Craig

28 Sep 11 at 1:12 am
Thanks mate, worked just fine

Sam

11 Oct 11 at 9:30 am