Multiple updates in a single update statement ??


I have to update all the records in one single table about, 500 records. Do i have to write 500 update statemements, or is there any elegant way to do it?

more ▼

asked Dec 21, 2010 at 05:51 AM in Default

avatar image

Katie 1
1.4k 132 164 205

Katie, please post the table you want to work on, the code you have a present (if any) and describe what criteria will dictate how the update should take place - we will gladly help.

Dec 21, 2010 at 05:57 AM WilliamD

Sure,Sorry about that, it was just a normal update statement, so i didnt paste the code in, update a.object1 set name = 'alias' where objectid = 1 , and the second update statement being

update a.object1 set name = 'alias2' where objectid = 2, like this i have about 500 records to update. these updates were written manually so, wondering if there was any other way i can avoid writing these updates statements manually.

Dec 21, 2010 at 06:03 AM Katie 1
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

If it concerns you, running multiple updates in one go, construct the query as a select

   when objectid = 1 then 'alias'
   when objectid = 2 then 'alias2'
  end as new_value_for_name
 from a.object1

see if the query returns what you want then convert it to an update

 update a.object1
 set name =  
   when objectid = 1 then 'alias'
   when objectid = 2 then 'alias2'
more ▼

answered Dec 21, 2010 at 07:58 AM

avatar image

Kev Riley ♦♦
66.1k 48 63 81

(comments are locked)
10|1200 characters needed characters left

It really depends. Are you updating 500 records with a basic set of data changes? Then @ThomasRushton already has your answer.

Or, do you have a whole bunch of different changes, let's say a unique change for each of the 500 rows? Then, you have some experimentation ahead of you. I've found, within the databases that I manage, that about 100-200 rows is the cut-off between techniques. Less than 200 rows, then 200 individual UPDATE statements wrapped in a transaction is faster. Above 200 rows, then passing the data to a query as an XML data set which I then use to JOIN to the table and perform an update is faster. But I think this is a situation where Your Mileage May Vary.

more ▼

answered Dec 21, 2010 at 06:42 AM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

(comments are locked)
10|1200 characters needed characters left

I know I am very late for this train, but I would like to take Grant's answer and elaborate on the points he makes as they make a lot of sense.

The answer really depends on the format of the deltas source. If you actually have the update hardwired such that the name column always gets a value of aliasN then Thomas has provided complete answer. If this is not the case then you can consider a couple of scenarios. I will use the names of the table and columns just as you have them in your question, so the table named dbo.a has 2 columns named objectid int and name varchar(50).

First scenario: the data representing your deltas can be serialized as xml. I will create a sample table and populate it with some dummy data for the sake of completeness:

 use AdventureWorks;
 -- create sample table
 create table dbo.a
     objectid int not null identity(1, 1)
         constraint PK_a primary key clustered,
     [name] varchar(50) null
 -- populate it with 1000 records such that the 
 -- value of name column is null in every record
 insert into dbo.a default values;
 go 1000
 -- create proc which will update your destination
 -- table using the xml data passed into the proc
 -- as xml type variable
 create proc dbo.usp_TableA_UpdateFromXml
     @xml xml
     set nocount on;
     -- if you only have to update hundreds of records at once
     -- then it is OK to join the xml directly, but if you need
     -- to update hundreds of thousands of records then it
     -- might make sense to insert the xml data into table
     -- variable (or temp table) first and only then issue 
     -- your final update from join. From what I have experienced
     -- this method is pretty fast provided that the size of
     -- your xml data does not go over 500 MB. Bigger sizes of
     -- xml data might become problematic :) 
 ;with deltas(objectid, [name]) as
         item.value('@objectid[1]', 'int') objectid,
         item.value('@name[1]', 'varchar(50)') [name]
         from @xml.nodes('//rows/r') R(item)
     update dbo.a
         [name] = deltas.[name]
         from dbo.a inner join deltas
             on a.objectid = deltas.objectid;
     set nocount off;

This completes the setup, and now you can test the proc:

 declare @xml xml;
 set @xml = '
         <r objectid="1" name="alias123" />
         <r objectid="2" name="alias234" />
         <r objectid="3" name="alias345" />
         <r objectid="4" name="alias456" />
         <r objectid="5" name="alias567" />
         <r objectid="6" name="alias678" />
         <r objectid="7" name="alias789" />
         <r objectid="8" name="alias890" />
         <r objectid="9" name="alias901" />
         <r objectid="10" name="alias101" />
 exec dbo.usp_TableA_UpdateFromXml @xml;

The table has as many records updated as there are nodes in that xml, so the results of the select from it will look like this:

 objectid    name
 ----------- --------
 1           alias123
 2           alias234
 3           alias345
 4           alias456
 5           alias567
 6           alias678
 7           alias789
 8           alias890
 9           alias901
 10          alias101
 11          NULL
 -- etc

The second scenario is to use the variable of table type and pass it as a parameter to the proc. This option is available because the database in question is SQL Server 2008. I will not discuss it here because there was a related question in June of this year to which I typed the answer which included a complete sample. I am not sure whether it was helpful or not as the answer does not have any upvotes and it has not been accepted, but still, you might want to look at it. Here is the link to this question.

Just my 2 cents


more ▼

answered Dec 21, 2010 at 08:35 AM

avatar image

18.9k 3 7 28

epic answer oleg +1.

Dec 21, 2010 at 11:09 AM WilliamD

@WilliamD Thank you very much. It is interesting that I never had any feedback from this link referenced on the bottom of my answer though I tried to pimp it quite few times already. Not that I am craving an upvote for that answer, but I would be really curious to read the experts' opinion about using the structured type combined with UDT table. I believe that this could be a useful technique in some scenarios, but never had a chance to give it a hard test yet (we are in the process of upgrading to SQL Server 2008 at work, but are not there yet).

Dec 21, 2010 at 11:22 AM Oleg
(comments are locked)
10|1200 characters needed characters left

You can do all at once.

I'll rephrase that. SQL Server doesn't mind whether you update one record of one field, or any number of records or fields.

As long as you can create an appropriate WHERE clause to restrict the rows you want to update, and as long as you can define the appropriate values with which to overwrite your existing data, then there's no problem.

If, on the other hand, you want to apply complex logic to work out what fields should be updated with what, then you might need to do a RBAR (that's Row By Agonizing Row, a phrase coined by one of the other chaps here...)

EDIT: Check MS's documentation on the UPDATE statement, as it may help.

more ▼

answered Dec 21, 2010 at 05:52 AM

avatar image

ThomasRushton ♦♦
42k 20 51 53

Thanks Thomas, how can we implement it?

Dec 21, 2010 at 05:55 AM Katie 1

Katie - first thing, check William's comment. Then we can think about the specifics.

Dec 21, 2010 at 05:58 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

Something along these lines should be what you want

 SET name = 'alias' + convert(varchar(5), objectid)
 UPDATE a SET name = 'alias' where name = 'alias1'

Not tested or anything...

more ▼

answered Dec 21, 2010 at 06:10 AM

avatar image

ThomasRushton ♦♦
42k 20 51 53

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 21, 2010 at 05:51 AM

Seen: 27666 times

Last Updated: Dec 21, 2010 at 05:51 AM

Copyright 2017 Redgate Software. Privacy Policy