Update fields that are not equal between 2 tables

[mflancour]

I have a table that will be pretty large, maybe 20 mil records. There are two basic things that happen to the table,
1. new records are added
2. existing records are updated based on new data

2 is what I am starting to work on now. any time we recieve new data where the primary key already exists in the main table, we need to check all fields in the main table against the new data to see; 1. which one is more current based on a date field, 2. if the new data is more current update the main table with the new data UNLESS the data for a field in the new data is null then skip that field and update the rest.

main table
primarykey field2 field3 datefield
1 NULL 'stuff' 1/1/2007
2 'asdf' 'asdff' 1/1/2007
4 'asdf' 'asdff' 1/1/2007
5 'asdf' 'asdff' 1/1/2007
6 'asdf' 'asdff' 1/1/2007

newdata
primarykey field2 field3 datefield
1 'asdf' 'NEWff' 1/1/2006
2 'NEWA' 'NEWWf' 1/5/2007
4 'NEWA' NULL 1/5/2007
5 'NEWA' 'NEWWf' 1/5/2007

main table (AFTER UPDATE)
primarykey field2 field3 datefield
1 NULL 'stuff' 1/1/2007
2 'NEWA' 'NEWWf' 1/5/2007
4 'NEWA' 'asdff' 1/5/2007
5 'NEWA' 'NEWWf' 1/5/2007
6 'asdf' 'asdff' 1/1/2007

 

[mrdenny]

Something like this will do the trick. It will probably take a while to run.

update main_table
set Field2 = case when a.Field2 is null then Field2 else a.Field2 end,
Field3 = case when a.Field3 is null then Field3 else a.Field3 end
from mnewdata a
where main_table.primarykey = newdata.primarykey
   and main_table.datefield < newdata.datefield

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com