I had a new developer this week ask me a good way to write an update statement with a join across two tables. He was new to set based processing only having worked with procedural logic in code. Set based allows you to update many rows all at the same time and is almost always faster than doing each row one by one. His suggestion of using a cursor to update every row just wouldn’t have worked well.
After explaining what set based processing was I also showed him a simple trick I’ve always used to write my set based update statements. I’ve added a simple script below and I’ll walk through what I typically do to write the statements.
The simplest method I’ve always found is to just write a normal select statement with the join’s you need and all the columns you want to see and then replace the select statement at the top with an update statement. This lets you see the data and test that the data is exactly what you want to update. I’ve enclosed a script showing this simple method and how I use it. Hopefully it will make your life a little easier as well.
———————————————————————–
–Simple Setup section
Create table #toBeUpdated
(id int identity(1,1) not null Primary Key,
Keycolumn varchar(255) not null default ‘Tasty’,
UpdateColumn varchar(255) null
)
Create table #UpdateValue
(id int identity(1,1) not null Primary Key,
Keycolumn varchar(255) not null default ‘Tasty’,
UpdateValueColumn varchar(255) null default ‘Bacon’
)
Insert into #toBeUpdated (Keycolumn) values (Default)
Insert into #UpdateValue (Keycolumn,UpdateValueColumn) values (Default,Default)
———————————————————————–
–How the original table looks
Select * from #toBeUpdated
———————————————————————–
–this shows what column needs to be updated and what it will be updated to
Select
Upd.Keycolumn,
Upd.Updatecolumn as SourceUpdateColumn,
Upv.UpdateValueColumn as TargetUpdateColumn
from
#toBeUpdated as Upd
join
#UpdateValue as Upv
on
upd.Keycolumn = upv.Keycolumn
———————————————————————–
–To create the update statement simply comment out the select statement and
–write the update at the top.
Update #toBeUpdated
Set UpdateColumn = Upv.UpdateValueColumn
–Select
– Upd.Keycolumn,
– Upd.Updatecolumn as SourceUpdateColumn,
– Upv.UpdateValueColumn as TargetUpdateColumn
from
#toBeUpdated as Upd
join
#UpdateValue as Upv
on
upd.Keycolumn = upv.Keycolumn
———————————————————————–
–shows the results of the Update
Select * from #toBeUpdated
