Bulk Record Update with SQL

Question

I have two tables in a SQL Server 2008 environment with the following structure

Table1
- ID
- DescriptionID
- Description

Table2
- ID
- Description

Table1.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?

score 69 · Accepted Answer · edited Jan 15 '20 at 16:31

69

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.ID

The nested select is the long way of just doing a join.

edited Jan 15 '20 at 16:31

Gronk

3
2

answered Jul 08 '11 at 18:56

Tocco

1,655
11
14

3

I like to use the alias in the UPDATE. No difference really but makes it clear which table in the FROM I'm actually updating. Useful for self joins etc – gbn Jul 08 '11 at 19:25
@gbn: You can't set an alias for a table that is just after the update clause (`UPDATE thisTableCannotHaveAnAlias`) ... – Tocco Jul 08 '11 at 19:32
4

You don't alias there, you refer to the alias you specify in the from clause. Like my answer and in an older one by me: http://stackoverflow.com/questions/982919/sql-update-query-using-joins/982947#982947 and on MSDN http://technet.microsoft.com/en-us/library/ms177523.aspx where you can `UPDATE table_alias` "table_alias: The alias specified in the FROM clause representing the table or view from which the rows are to be updated." – gbn Jul 08 '11 at 19:46

gbn · Answer 2 · 2011-07-08T19:03:19.970

33

Your 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.DescriptionID

Both this and your query should run the same performance wise because it is the same query, just laid out differently.

edited Jul 08 '11 at 19:03

answered Jul 08 '11 at 18:57

gbn

422,506
82
585
676

7

+1 For telling him perf is the same. Yours is easier to read by far, though. – JNK Jul 08 '11 at 19:02

score 3 · Answer 3 · edited Mar 12 '15 at 10:12

3

Or 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.ID

edited Mar 12 '15 at 10:12

bummi

27,123
14
62
101

answered Mar 12 '15 at 09:50

Ejaz Waquif

61
3

score 3 · Answer 4 · answered Jul 08 '11 at 18:57

3

You 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.DescriptionId

answered Jul 08 '11 at 18:57

George Johnston

31,652
27
127
172

This looks exactly like gbn's answer, yet @Tocco says this one is wrong and the other is correct. What gives? – Doug S Aug 10 '14 at 03:10

score 0 · Answer 5 · edited Oct 03 '19 at 19:33

The 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.

Bulk Record Update with SQL

5 Answers5

Linked