CODEWITHSUNDEEP
sqlsql-server-2008
Jimmy Chiu
Jimmy Chiu

Reputation: 1

sql query update row

I am trying to have all the null address populated by looking at the CaseID and find the first matching non-null address. Thank you for helping.

Some table:

IndexId   CaseId    Address  
------    -------   ------------
1         10        1 ABC STREET  
2         10        NULL
3         10        NULL
4         20        NULL
5         20        100 TEST STREET
6         20        NULL
7         30        NULL
8         30        NULL
9         30        900 DEV AVENUE

Result table:

IndexId   CaseId    Address  
------    -------   ------------
1         10        1 ABC STREET  
2         10        1 ABC STREET  
3         10        1 ABC STREET  
4         20        100 TEST STREET
5         20        100 TEST STREET
6         20        100 TEST STREET
7         30        900 DEV AVENUE
8         30        900 DEV AVENUE
9         30        900 DEV AVENUE

Upvotes: 0

Views: 37

Answers (3)

Markus
Markus

Reputation: 1

Assume the table is called @t:

SELECT t1.*
FROM @t AS t1
    LEFT JOIN @t AS t2 ON t2.CaseID = t1.CaseID
WHERE t1.Address is not null

Upvotes: 0

jarlh
jarlh

Reputation: 44795

Set to first alphabetically:

update tablename t1 set Address = (select min(Address) from tablename t2
                                   where t2.CaseId = t1.CaseId)
where Address is null

Or first accordning to indexid:

update tablename t1 set Address = (select top 1 Address from tablename t2
                                   where t2.CaseId = t1.CaseId
                                     and ADDRESS IS NOT NULL
                                   order by indexid)
where Address is null

EDIT: missed is not null in sub-query, thanks TZHX.

Upvotes: 0

TZHX
TZHX

Reputation: 5398

UPDATE Table T1 
SET T1.Address = 
    ( SELECT TOP 1 T2.Address FROM Table T2 
        WHERE T2.CaseId = T1.CaseId AND T2.Address IS NOT NULL ) 
WHERE T1.Address IS NULL

Upvotes: 1

Related Questions