TSQL Update statement with Join

问题:

I have two tables with a 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 pick which child is used for the update depending on some other column from the child. Here is what I have so far: Parent: @test Child: @exdat Expected outcome, result of parent after update should contain only Capital letters. I want to update the parent with some data from the child, but if more than one exists I'd rather choose a child with a dimp of 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 have been any of the values w,g,G,z. So I am asking how can I prioritize the child selection based on some other column ?

回答1:

What you want to do instead of a join is a subquery. Something like this:

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's how you specify precedence
   ), 'ReasonableDefault')
FROM
   @test a

回答2:

Trying using a CROSS APPLY with your update. The example below orders by the @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 would look like this:

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

You can modify this by changing the order by in your CROSS APPLY

回答3:

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

回答4:

I think using the ranking functions can be put to good use here.

In this sample I used DENSE_RANK to pick the highest dimp value by ORDER BY dimp and then looking for the one had the rank of 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;