update table with Join

it’s easy to update an existing sql table. In T-sql:

update table set column1 = 'something'

This updates all column1 values to ‘something’

More typically, you want to update only SOME rows, so you would use a where predicate

update states set ST to 'NY" where ST = 'New York'

or

update sales set discount = discount + .1 where client_type = 'super saver'

What i need to do is to update table 1 based on a matching value in table 2. The syntax goes all to hell.

Update TABLE set TABLE.column1 = TABLE_2.column1 
FROM theFirstTable as TABLE
JOIN theSecondTable as TABLE_2 on TABLE.ID = TABLE_2.ID
where ...

I have this big old table that takes nearly an hour to build. I needed to add three columns to the schema – which actually involved three separate joins on the same second table (three times since i needed to get three different join conditions based on the id). With the additional joins, we got timeout and resource errors. Might not have been the complexity, but having activity supply disorder, i wanted to get it done asap.

I created the three new columns as “,null ColumnName”. After running the creation event, i had my original table with three new columns, each with the proper Name and each filled with nulls. Next, rather than the three joins to the Name table, i ran an update on the original table three times. Update the big table Name1 column with the matching value from the second table. Then update the big Table Name2 column with the matching value from the second table. Rather than dying from resources after ten minutes on several separate runs, i got my big table with null, and each of the three updates took 5 minutes for 5 millions rows.

My actual code, in dynamic SQL, looks like this

set @cmd = 'update pay
set pay.Referring_dr_id = pm.pm_provider_id 
from ' + @dbname + '.dbo.qt_service_billing_payments3 pay
join ' + @dbname + '.dbo.pm_providers pm 
on pm.abbreviation = pay.Referring_Dr_Abbr
where pm.abbreviation = pay.billing_dr_abbr'
-- exec(@cmd)

In hindsight,  you could do it in one pass with case logic.

Advertisements