CODEWITHSUNDEEP
sql-server-2008
UnKnown
UnKnown

Reputation: 1

SQL Server 2008: Update columns if its a null

I have 100 columns in my table and I want to update only columns that have NULL value.

I am comparing master table columns with temp table and trying to update master table column value with temp table column value.

Please help me in this regards.

Thanks in advance

Upvotes: 0

Views: 2635

Answers (3)

Random
Random

Reputation: 1956

UPDATE B SET B.value = T.value
FROM
tblMaster B 
   INNER JOIN tblTemp T ON B.ID = T.ID
WHERE B.value IS NULL

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

The following should update a row in the master table with either its previous value (if it is not null) or the value from the temp table if the value of the master table is null.

This requires that master and temp table can be joined on a key field named Key in my case.

UPDATE m
SET
  m.field1 = ISNULL(m.field1, t.field1), 
  ...
FROM
  MasterTable m
  INNER JOIN TempTable t ON t.Key = m.Key

Upvotes: 0

HLGEM
HLGEM

Reputation: 96542

Something like:

Update t1
set field1 = coalesce(t1.field1, 'test')
  , field2 = coalesce(t1.field2, t1.field1)
  , field3 = coalesce(t1.field3, t2.field1)
  , field4 = coalesce(t1.field4, t2.field1, t2.field3)

FROM table1 t1
join table2 t2
 on t1.someid = t2.someId

I have given you three examples of differnt ways you might update if the field is null. The first shows how to set it to a text value, the second how to set it to another field in the same table and third is the one where you get the value from a different table. The forth shows what to do if the value you are setting it to is also nul and thus want to use still another value in its place. You will need to write a coalesce update for each of the 100 columns.

Upvotes: 2

Related Questions