SQL Server Syntax to select two rows of data and overwrite values that are different from the first row

Question

Hi,

I have a table that looks like the following:

id,  Name,     LinkBackId, Param1, Param2, Param3
1,  "Name1",   NULL,       10,     20,     30    
2,  "Name2",   NULL,       10,     20,     30 
3,  "Name3",   2,          14,     NULL,   NULL

The LinkBackId is important here because this is associated with the id that is also in this table. Looking at row 3 LinkBackId contains 2 I want it to return the row from id = 0 and merge the values from row 3 into the result like this:

3,  "Name3", 2, 10, 20, 30

I am treating the rows that contain NULL in the LinkBackId as a "master" row of parameters. The rows that contain a linkbackid are to be overriden by the master values that are not NULL.

I am wondering if there is an elegant method to do this instead of using lots of if statements.

Hope this makes sense.

Thank you

Answer 1

Here's a query that will return the values from a row, unless it has a LinkBackId value, in which case it will use values from that row:

SELECT 
  t1.id,
  t1.Name,
  t1.LinkBackId,
  COALESCE(t2.Param1,t1.Param1),
  COALESCE(t2.Param2,t1.Param2),
  COALESCE(t2.Param3,t1.Param3)
FROM table t1
LEFT OUTER JOIN table t2
  ON t1.LinkBackId = t2.id

It won't work with multiple levels of nesting (i.e. if row 2 referred back to row 1). It works by using the COALESCE function, which returns the parameter from the primary row if there are no values in the LinkBackId row.

Answer 2

If you want to update row 2 with the values from row 3?

if so, then try this

Update tu Set 
    name = o.Name,
    linkbackId = o.LinkbackId,
    param1 = o.param1,
    param2 = o.param3,
    param3 = o.param3
From Table tu 
  Join Table tr
     On tr.LinkbackId = tu.Id
Where tu.id = 2

EDIT: Not what you want, sorry