I have 2 tables A and B.

A is like

The amounts in the fields Value Category X are not correct. I would like to update these amounts thanks to table B, which is like

Do you have an idea how to proceed? The difficulty here comes from the fact that we have 3 fields for the Values in table A and only 1 field for the value in table B.

CodePudding user response：

You can try something like this then.

DECLARE @table_a AS TABLE ( A_Policy INT, First INT, Second INT, Third INT )

DECLARE @table_b AS TABLE ( B_Policy INT, Cat INT, Value INT )

INSERT INTO @table_a ( A_Policy, First, Second, Third )
VALUES ( 10, 15000, 2000, 75000 )
,      ( 11, 12500, 2500, 7000 )

INSERT INTO @table_b ( B_Policy, Cat, Value )
VALUES ( 10, 1, 11000 )
,      ( 10, 2, 150 )
,      ( 10, 3, 8500 )
,      ( 11, 1, 12500 )

SELECT  *
  FROM  @table_a

UPDATE  a
   SET  First = Value
  FROM  @table_a a
  JOIN  @table_b b ON   a.A_Policy = b.B_Policy
                  AND   b.Cat = 1

UPDATE  a
   SET  Second = Value
  FROM  @table_a a
  JOIN  @table_b b ON   a.A_Policy = b.B_Policy
                  AND   b.Cat = 2

UPDATE  a
   SET  Third = Value
  FROM  @table_a a
  JOIN  @table_b b ON   a.A_Policy = b.B_Policy
                  AND   b.Cat = 3

SELECT  *
  FROM  @table_a

Not the most graceful approach, but if there are only three updates, in the real world this is a quick and workable solution. Scalability will depend on how many rows you have.

CodePudding user response：

You can JOIN the source table multiple times with itself to "pivot" it into the form that you need:

SELECT DISTINCT t0.B_Policy
              , t1.Value as Value_Category_1
              , t2.Value as Value_Category_2
              , t3.Value as Value_Category_3 
  FROM tab_b t0
  LEFT JOIN tab_b t1 ON t0.B_Policy = t1.B_Policy AND t1.category = 1   
  LEFT JOIN tab_b t2 ON t0.B_Policy = t2.B_Policy AND t2.category = 2 
  LEFT JOIN tab_b t3 ON t0.B_Policy = t3.B_Policy AND t3.category = 3 
;

You can then use that to MERGE it into your target table.

In one statement

WITH b_pivot AS 
  (  SELECT DISTINCT t0.B_Policy, t1.Value as Value_Category_1, t2.Value as Value_Category_2, t3.Value as Value_Category_3 
       FROM tab_b t0
  LEFT JOIN tab_b t1 ON t0.B_Policy = t1.B_Policy AND t1.category = 1   
  LEFT JOIN tab_b t2 ON t0.B_Policy = t2.B_Policy AND t2.category = 2 
  LEFT JOIN tab_b t3 ON t0.B_Policy = t3.B_Policy AND t3.category = 3 
  )
MERGE tab_a
USING b_pivot
   ON tab_a.A_Policy = b_pivot.B_Policy
WHEN MATCHED 
THEN UPDATE SET tab_a.Value_Category_1 = COALESCE(b_pivot.Value_Category_1, tab_a.Value_Category_1),
                tab_a.Value_Category_2 = COALESCE(b_pivot.Value_Category_2, tab_a.Value_Category_2),
                tab_a.Value_Category_3 = COALESCE(b_pivot.Value_Category_3, tab_a.Value_Category_3)
;

Result:

See db<>fiddle.