Solved

Updating multiple rows in a single statement

Posted on 2006-11-16
15
9,349 Views
Last Modified: 2011-08-18
Hey all!

I thought I'd have a look at trying to make our update queries more efficient, because our code currently generates multiple update queries when I'd rather just operate on multiple rows at the same time.

I've tried both of the revised queries below, because (to me), they seemed to be the most logical. Neither of them work, however. Immediately below is the syntax which is currently used and requires three seperate queries to complete all the updates.

Original query:
UPDATE ord_products SET modelcode='noo1' WHERE (quoteID='foo1');
UPDATE ord_products SET modelcode='noo2' WHERE (quoteID='foo2');
UPDATE ord_products SET modelcode='noo3' WHERE (quoteID='foo3');

Revised queries:
UPDATE ord_products SET (modelcode='noo1' WHERE quoteID='foo1'),(modelcode='noo2' WHERE quoteID='foo2'),(modelcode='noo3' WHERE quoteID='foo3');

OR

UPDATE ord_products (modelcode) VALUES('noo1'),('noo2'),('noo3') WHERE (quoteID IN ('foo1','foo2','foo3'));


Can anyone suggest a solution for this to run this in one query?

Thanks in advance.
0
Comment
Question by:DanBAtkinson
  • 5
  • 5
  • 2
  • +3
15 Comments
 
LVL 27

Expert Comment

by:yodercm
ID: 17955495
I do not believe that you can do this in one query, sorry.
0
 

Author Comment

by:DanBAtkinson
ID: 17955513
Boo! SQL Server rules!

Seriously now, you can't?

That... That really sucks!

I'll wait around for a little while and see if someone can offer a solution (which doesn't involve a stored procedure, which would be admitting defeat).
0
 
LVL 14

Accepted Solution

by:
mherchl earned 500 total points
ID: 17955701
UPDATE ord_products
   SET modelcode = case quoteID when 'foo1' then 'noo1'
                                when 'foo2' then 'noo2'
                                when 'foo3' then 'noo3'
                   end
 WHERE quoteID in ('noo1', 'noo2', 'noo3')
                     
0
 
LVL 14

Expert Comment

by:ppfoong
ID: 17956919

UPDATE ord_products  SET modelcode = CASE quoteID
  WHEN 'foo1' THEN 'noo1'
  WHEN 'foo2' THEN 'noo2'
  WHEN 'foo3' THEN 'noo3'
ELSE modelcode END

0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 17957064
Temporary tables to the rescue!  It's 1 big insert statement + 3 small statements, but it avoids doing hundreds of separate update statements.

CREATE TEMPORARY TABLE tmp_ord_products_update (tmp_modelcode varchar(32) not null, tmp_quoteID varchar(32) not null PRIMARY KEY USING BTREE);
;; make sure those datatypes match your types in ord_products;
INSERT INTO tmp_ord_products_update (tmp_modelcode, tmp_quoteID) VALUES ('noo1', 'foo1'), ('noo2', foo2'), ('noo3', 'foo3').... and so on;
UPDATE ord_products, tmp_ord_products_update set modelcode = tmp_modelcode where quoteID = tmp_quoteID;
DROP TEMPORARY TABLE tmp_ord_products_update;
0
 

Author Comment

by:DanBAtkinson
ID: 17957096
Thankyou both for your help.

This is amazingly helpful!

How would I do this with more than one field?

EG editing modelcode and... foobar.

0
 

Author Comment

by:DanBAtkinson
ID: 17957103
NovaDenizen. Temporary tables are not a solution I'm looking at I'm afraid. But thankyou for your input.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 22

Expert Comment

by:NovaDenizen
ID: 17957166
Another way of doing it:

UPDATE ord_products  SET modelcode =
CASE quoteID
  WHEN 'foo1' THEN 'noo1'
  WHEN 'foo2' THEN 'noo2'
  WHEN 'foo3' THEN 'noo3'
ELSE modelcode END
0
 

Author Comment

by:DanBAtkinson
ID: 17957184
NovaDenizen, that's exactly the same as ppfoong's.
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 17957230
Whoops, sorry.

Another way of doing it:
UPDATE ord_products  SET modelcode =
    IFNULL(ELT(FIELD(quoteID, 'foo1', 'foo2', 'foo3'),
                      'noo1', 'noo2', 'noo3'),
               modelcode);
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 17957245
I hit submit too early before, hit escape to cancel, but evidently it submitted.
0
 
LVL 27

Expert Comment

by:yodercm
ID: 17957332
Well, I have learned something, too :))

**Wipes egg off face**

Good luck!
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 17957502
It also might speed things up for any of our single-statement solutions if you tack this on at the end.  I don't think mysql is smart enough to automatically optimize it down for the desired list of quoteID's.

... WHERE quoteID in ('foo1', 'foo2', 'foo3');
0
 
LVL 31

Expert Comment

by:awking00
ID: 17958343
>>How would I do this with more than one field?<<

UPDATE ord_products  SET (modelcode, anotherfield) =
CASE quoteID
  WHEN 'foo1' THEN 'noo1'
  WHEN 'foo2' THEN 'noo2'
  WHEN 'foo3' THEN 'noo3'
ELSE modelcode END,
case when condition1 then value1
       when condition2 then value2
       when condition3 then value3
else anotherfield end


0
 

Author Comment

by:DanBAtkinson
ID: 17963474
Thanks. These were very helpful.

As mherchl provided the first solution, that is the accepted answer.

Thanks everyone for your input and suggestions!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now