If it concerns you, running multiple updates in one go, construct the query as a select select name, case 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 = case when objectid = 1 then 'alias' when objectid = 2 then 'alias2' ... ... ... end
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. :
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.
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; go -- create sample table create table dbo.a ( objectid int not null identity(1, 1) constraint PK_a primary key clustered, [name] varchar(50) null ); go -- 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 ) as begin; 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 ( select item.value('@objectid', 'int') objectid, item.value('@name', 'varchar(50)') [name] from @xml.nodes('//rows/r') R(item) ) update dbo.a set [name] = deltas.[name] from dbo.a inner join deltas on a.objectid = deltas.objectid; set nocount off; end; go This completes the setup, and now you can test the proc: declare @xml xml; set @xml = ' '; 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 Oleg :