Solved

Updating multiple rows in a single statement

Posted on 2006-11-16
15
9,370 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 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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