I am trying to update multiple rows at once (SQL Server 2005). Previously I was updating a single row with this query:
UPDATE dbo.My_Users
SET MyUserId = @MyUserId
WHERE EmailID = @EmailId
Now @EmailId will have comma-separated EmailIds.
How can I modify the script to update multiple rows? I have seen some examples which make use of UNION ALL. But they are mostly to insert multiple records without where clause.
A similar question was answered in Parameterize an SQL IN clause
The same idea can be applied here:
declare @EmailIds varchar = '|email1@test.com|email2@test.com|';
UPDATE dbo.My_Users SET MyUserId=@MyUserId WHERE @EmailIds LIKE '%|' + EmailID + '|%';
Though this does not contain a comma-separated list, the delimiter could easily be changed to a pipe-character. The caveat here is, the more data that exists in the table and the more email addresses that are in the @EmailIds list, the slower (much slower) this query can become.
Using C#, I would actually recommend the second example in the above-mentioned question where the list is expanded to create a query similar to:
UPDATE dbo.My_Users SET MyUserId=@MyUserId WHERE EmailID IN (@email1, @email2);
C# to implement (a modified version of the example in the question above):
string[] emails = new string { "email1@test.com", "email2@test.com" };
string sql = "UPDATE dbo.My_Users SET MyUserId=@MyUserId WHERE EmailID IN ({0});"
string[] emailParams = emails.Select((s, i) => "@email" + i.ToString()).ToArray();
string inClause = string.Join(",", emailParams);
using (SqlCommand cmd = new SqlCommand(string.Format(sql, inClause))) {
for(int i = 0; i < emailParams.Length; i++) {
cmd.Parameters.AddWithValue(emailParams[i], emails[i]);
}
}
You could use dynamic SQL
exec('UPDATE dbo.My_Users SET MyUserId = ' + cast(@MyUserId as varchar) + ' WHERE EmailID in (' + @EmailIds + ')')
