Update Single Field Where Have Unknown Fields. 2

[ScrewBalll]

Hi there, I'm in need of some help regarding updating an sql table from a staging table. I have a master table with approximately 25 populated fields, now this table is being sent updates which are placed into a staging table. The problem I have is that to save resources only ammended details are placed into the staging table with the key fields.

For example:
KEY; FIELD1; FIELD2; FIELD3; FIELD4
DATA DATA DATA DATA DATA (MASTER TABLE)
DATA NULL NULL NULL DATA (Staging table)

In this example I only want to ammend Field4. If i carry out a complete update fields 1-3 are updated to null which isn't right. I will not know what fields have been ammended beforehand so the only way I can think to carry this out is to use lots of 'IF NOT ISNULL' statements. But with the amount of data I'm being passed this takes up to many resources. I'm hoping for a more efficient method if possible.

Please help.

 

[hmckillop]

You could try and include it in your update statement using case clause.

Update tb1
set tb1.field1 = case when tb2.field1 is not null then tb2.field1 else tb1.field1 end,
set tb1.field2 = case when tb2.field2 is not null then tb2.field2 else tb1.field2 end
--etc
FROm mytable tb1
inner join MasterTable tb2 on tb1.key = tb2.key

"I'm living so far beyond my income that we may almost be said to be living apart

 

[ScrewBalll]

Thanks for you quick response, I've just checked it with my table and it works great. Simple yet effective.

 

[hmckillop]

only too glad to help

"I'm living so far beyond my income that we may almost be said to be living apart

 

[LFCfan]

you can also use the ISNULL function:

Update tb1
set 
    tb1.field1 = ISNULL(tb2.field1,tb1.field1),
    tb1.field2 = ISNULL(tb2.field2,tb1.field2)
FROM....