SQL Server: dynamically updating multiple rows

Question

I want to update several rows in my database, the values ​​may vary. Present this table:

Username FirstName LastName ------------------------------- user1 John Doe user2 Jane Doe user3 bill gates 

If I want to update the table, it looks like this:

 Username FirstName LastName ------------------------------- user1 John Deer user2 Jane Farmer user3 Gill Bates 

Is it better to use one UPDATE for each user? Or can this be done with a single request?

You can also put it in a stored procedure and provide a collection of values?

Second question: is it better to check if the values ​​are changed before performing the update? Or just update each, even if the value is the same?

I am currently using SQL Server 2005 and C # for the application, if that matters.

Answer 1

You can try this

 declare @TempTable1 table (UserName nvarchar(128) primary key, FirstName nvarchar(128), LastName(128)) insert into @TempTable1 select 'user1', 'John', 'Deer' union all select 'user2', 'Jane', 'Farmer' union all select 'user3', 'Gill', 'Bate' update table1 set FirstName = t2.FirstName, LastName = t2.LastName from table1 as t inner join @TempTable1 as t2 on t2.UserName = t.UserName 

Or if you want to update only changed fields

 update table1 set FirstName = isnull(t2.FirstName, t.FirstName), LastName = isnull(t2.LastName, t.LastName) from table1 as t inner join @TempTable1 as t2 on t2.UserName = t.UserName 

For a C # client application, I believe the usual way is to create a procedure

 create procedure sp_Table1_Update ( @UserName nvarchar(128), @FirstName nvarchar(128), @LastName nvarchar(128) ) as begin update table1 set FirstName = @FirstName, LastName = @LastName where UserName = @UserName end 

and then call it from the application for each user

Answer 2

I would create separate queries for individual users:

 UPDATE Table1 SET FirstName = 'John', 'LastName = 'Doe' WHERE FirstName = 'John' AND 'LastName = 'Deer' AND 'UserName = 'user1' 

and etc.

You have unique usernames, so theoretically you do not need to update values ​​that have not changed. However, you can always create it as an extra precaution.