CODEWITHSUNDEEP
sqlsql-server
Jeff
Jeff

Reputation: 882

Update Fields - SQL Server

In SQL Server, I am trying to update null fields in a table with existing vales in the table.

Example:

Table has 4 columns:A,B,C,D

A     B     C     D
1     2     3     4
5     Null  Null  4

How can I populate the Null values with the values in B,C where D=4

Update Table
Set B=B
Where B is null
and D=4

Upvotes: 0

Views: 47

Answers (1)

sgeddes
sgeddes

Reputation: 62861

One option is to use a self join:

update t
set t.b = t2.b
from yourtable t
    join yourtable t2 on t.d = t2.d 
                    and t2.b is not null
where t.b is null

What if multiple records exist for b grouped by d where b is not null? That could mess this up. Instead you'd have to decide which value to use. Here's an example choosing the min:

update t
set t.b = t2.b
from yourtable t
    join (select d, min(b) b 
          from yourtable
          where b is not null
          group by d) t2 on t.d = t2.d 
where t.b is null

Or with a correlated subquery:

update yourtable t
set b = (select min(b) from yourtable t2 where t.id = t2.id)
where t.b is null

A lot of options here...

Upvotes: 1

Related Questions