CODEWITHSUNDEEP
sqlsql-server
Rod
Rod

Reputation: 15433

how to update a field based on existing records in the same table

Environment: SQL Server 2012

I need help with a maintenance query to update the 4 nulls in the following illustration. I also have a sqlfiddle to look at (edited)

In this example, the first SideSort null should be 7.0 because there is an existing SideId of 1 that has a value to use. The second should be 8.0.

The same goes for TopSort, however, if there isn't an existing TopId or SideId just default to 1.

enter image description here

Upvotes: 2

Views: 70

Answers (2)

ARA
ARA

Reputation: 1316

if the SideId is the not the id of the SideSort record to use, join is done on same SideId. the update is:

update t1 
set t1.sidesort = coalesce(t2.sidesort,1)
FROM Tracker t1
  left join Tracker t2 
           on t2.SideId = t1.sideid
           and t2.SideId is not null
where t1.sidesort is null   

update t1 
set t1.topsort = coalesce(t2.topsort,1)
FROM Tracker t1
  left join Tracker t2 
           on t2.topid = t1.topid
           and t2.topId is not null
where t1.topsort is null

Upvotes: 1

Szymon
Szymon

Reputation: 43023

You can so it this way:

update Tracker
set topSort = (select top 1 isnull(topSort, 1) from Tracker T where T.topId = Tracker.topId)
where topSort is null

update Tracker
set sideSort = (select top 1 isnull(sideSort, 1) from Tracker T where T.sideId = Tracker.sideId)
where sideSort is null

As there are multiple values for the same topSort and sideSort, I just return top 1 in the inner query but you may want to replace it with something else that will return just one record.

Upvotes: 1

Related Questions