Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Updating multiple rows in a single statement

Posted on 2006-11-16
15
Medium Priority
?
9,379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +3
15 Comments
 
LVL 27

Expert Comment

by:Cornelia Yoder
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 2000 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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:Cornelia Yoder
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

730 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