Solved

Updating multiple rows in a single statement

Posted on 2006-11-16
15
9,354 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

829 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