TSQL update operation with union

Question

I have two tables with parent / child relationship. I want to update the parent with data from the child. However, assuming there are 2 children, I would like to be able to choose which child is used to update, depending on the other column from the child. Here is what I have so far: Parent: @test Child: @exdat Expected result, the result of the parent after the update should contain only uppercase letters. I want to update a parent with some data from a child, but if more than one exists, I would prefer to select a child with dimp p1 over p2, p2 over p3 and p3 over p4

DECLARE @test TABLE ( id int, val char(1) ); DECLARE @exdat TABLE ( id int, dval char(1), dimp char(2) ); INSERT INTO @test (id,val) SELECT 1,'a' UNION ALL SELECT 2,'b' UNION ALL SELECT 3,'c' UNION ALL SELECT 4,'d' UNION ALL SELECT 5,'e' UNION ALL SELECT 6,'f' UNION ALL SELECT 7,'g' ; INSERT INTO @exdat (id,dval,dimp) SELECT 1,'A','p1' UNION ALL SELECT 2,'B','p3' UNION ALL SELECT 3,'C','p1' UNION ALL SELECT 4,'D','p2' UNION ALL SELECT 5,'E','p2' UNION ALL SELECT 6,'F','p3' UNION ALL SELECT 7,'w','p2' UNION ALL SELECT 7,'g','p3' UNION ALL SELECT 7,'G','p1' UNION ALL SELECT 7,'z','p4' ; UPDATE @test SET val = e.dval FROM @test t INNER JOIN @exdat e ON t.id = e.id ; SELECT * FROM @test; Result: 1 A 2 B 3 C 4 D 5 E 6 F 7 w <-- problem illustrated here 

This "w" could be any of the values ​​w, g, G, z. So I ask, how do I prioritize a child’s choice based on another column?

Answer 1

What you want to do, not a connection, is a subquery. Something like that:

 UPDATE a SET a.val = ISNULL(( SELECT TOP 1 x.dval FROM @exdat x WHERE x.id = a.id ORDER BY x.magic_field -- <- here how you specify precedence ), 'ReasonableDefault') FROM @test a 

Answer 2

Trying to use CROSS APPLY with your update. An example of below orders by @ extdat.dimp value:

 DECLARE @test TABLE ( id int, val char(1) ); DECLARE @exdat TABLE ( id int, dval char(1), dimp char(2) ); INSERT INTO @test (id,val) SELECT 1,'a' UNION ALL SELECT 2,'b' UNION ALL SELECT 3,'c' UNION ALL SELECT 4,'d' UNION ALL SELECT 5,'e' UNION ALL SELECT 6,'f' UNION ALL SELECT 7,'g' ; INSERT INTO @exdat (id,dval,dimp) SELECT 1,'A','p1' UNION ALL SELECT 2,'B','p3' UNION ALL SELECT 3,'C','p1' UNION ALL SELECT 4,'D','p2' UNION ALL SELECT 5,'E','p2' UNION ALL SELECT 6,'F','p3' UNION ALL SELECT 7,'w','p2' UNION ALL SELECT 7,'g','p3' UNION ALL SELECT 7,'G','p1' UNION ALL SELECT 7,'z','p4' ; UPDATE @test SET t.val = e.dval FROM @test as t CROSS APPLY ( SELECT TOP(1) * FROM @exdat as cae WHERE t.id = cae.id ORDER BY cae.dimp ) as e ; SELECT * FROM @test; 

If you use this method, your results will look like this:

 Result: 1 A 2 B 3 C 4 D 5 E 6 F 7 G 

You can change this by changing the order in CROSS APPLY

Answer 3

I think the use of ranking functions can be used here.

In this example, I used DENSE_RANK to select the highest dimp value using ORDER BY dimp, and then to search was rank one.

 WITH cte AS (SELECT Dense_rank() OVER (PARTITION BY id ORDER BY dimp) AS foo, * FROM @exdat) UPDATE @test SET val = e.dval FROM @test t INNER JOIN cte e ON t.id = e.id WHERE foo = 1; 

Answer 4

 UPDATE @test SET t.val = e.dval FROM @test t JOIN @exdat e ON t.id = e.id JOIN ( SELECT id , MIN(dimp) AS dimp --orders by dimp ascending FROM @exdat WHERE dval = UPPER(dval) --keeps only rows with capital letters in dval GROUP BY id ) AS g ON e.id = g.id AND e.dimp = g.dimp