Bulk Record Update with SQL
Emily Wong 
I have two tables in a SQL Server 2008 environment with the following structure
Table1
- ID
- DescriptionID
- Description
Table2
- ID
- DescriptionTable1.DescriptionID maps to Table2.ID. However, I do not need it any more. I would like to do a bulk update to set the Description property of Table1 to the value associated with it in Table2. In other words I want to do something like this:
UPDATE [Table1]
SET [Description]=(SELECT [Description] FROM [Table2] t2 WHERE t2.[ID]=Table1.DescriptionID)However, I'm not sure if this is the appropriate approach. Can someone show me how to do this?
5 Answers
Your way is correct, and here is another way you can do it:
update Table1
set Description = t2.Description
from Table1 t1
inner join Table2 t2
on t1.DescriptionID = t2.IDThe nested select is the long way of just doing a join.
3Your approach is OK
Maybe slightly clearer (to me anyway!)
UPDATE T1
SET [Description] = t2.[Description]
FROM Table1 T1 JOIN [Table2] t2 ON t2.[ID] = t1.DescriptionIDBoth this and your query should run the same performance wise because it is the same query, just laid out differently.
1You can do this through a regular UPDATE with a JOIN
UPDATE T1
SET Description = T2.Description FROM Table1 T1 JOIN Table2 T2 ON T2.ID = T1.DescriptionIdOr you can simply update without using join like this:
Update t1 set t1.Description = t2.Description from @tbl2 t2,tbl1 t1
where t1.ID= t2.IDThe SQL you posted in your question is one way to do it. Most things in SQL have more than one way to do it.
UPDATE [Table1]
SET [Description]=(SELECT [Description] FROM [Table2] t2 WHERE t2.[ID]=Table1.DescriptionID)If you are planning on running this on a PROD DB, it is best to create a snapshot or mirror of it first and test it out. Verify the data ends up as you expect for a couple records. And if you are satisfied, run it on the real DB.
