Solved

Updating multiple rows in a single statement

Posted on 2006-11-16
15
9,358 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 Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

751 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