Search code examples
sqlsql-serversql-updatetemp-tablesinsert-update

SQL UPDATE on with data from same table

I have a next situation with a table, Need to update rows from rows in same table, as you see here:

TABLE

ID SN FID
1 12345 1
2 1122 2
3 12345-RG NULL
4 1122-RG NULL

I need to UPDATE row 3 from row 1 Column FID(Same SN, different ending only on the end -RG) Update row 4 FROM row 2 Column FID(Same SN, different ending -RG)

So the result should be:

TABLE

ID SN FID
1 12345 1
2 1122 2
3 12345-RG 1
4 1122-RG 2

I have tried many ways but I don't get with this... I tried declaring a temp table and trying to compare from there but still with this issue...

Solution

  • Asuming [SN] is being augmented with -RG

    Example

    with cte as (
Select * 
      ,NV = max(FID) over (partition by replace(SN,'-RG','') )
 From YourTable
)
Update cte set FID = NV 
 --Where FID is null  -- Optional

    The Updated Table

    ID  SN          FID
1   12345       1
2   1122        2
3   12345-RG    1
4   1122-RG     2