SQL Server 2017: update 2 columns in a table simultaneously with data from another table doesn'-CodePudding

I'm using Microsoft SQL Server 2017. I have a problem with an update of a table using values from another table.

I use the following setup:

Table "Main":

ID	Val1	Val2
42	NULL	NULL

Table "Val":

MainID	Type	Val
42	1	345.67
42	2	567.89

Then I want to update (in 1 statement) the Val1 and Val2 columns in "Main" with the values from "Val":

UPDATE m
SET m.Val1 = CASE WHEN v.Type = '1' THEN v.Val ELSE m.Val1 END,
    m.Val2 = CASE WHEN v.Type = '2' THEN v.Val ELSE m.Val2 END
FROM Main m 
INNER JOIN Val v ON m.ID = v.MainID

Val1 via Type 1 should be updated to 345.67 and Val2 via Type 2 should be updated to 567.89, so that the desired result should look as follows:

Table "Main":

ID	Val1	Val2
42	345.67	567.89

However, the actual result is:

Table "Main":

ID	Val1	Val2
42	345.67	NULL

What am I doing wrong here? Any ideas?

Please also take advantage of the SQLFiddle example here: http://sqlfiddle.com/#!18/ab9bf0/14

I know there are many similar questions on Stackoverflow already, but always doing something slightly different, so unfortunately they were of no help to me. If you found one that might solve my issue I'm more than happy to check it out.

Thanks very much in advance.

CodePudding user response：

use sub-query. Calculate the GROUP BY query in the sub-query and the join to Main table

UPDATE m
SET    m.Val1 = v.Val1,
       m.Val2 = v.Val2
FROM   Main m 
INNER JOIN 
(
    SELECT MainID,
           Val1 = MAX(CASE WHEN Type = '1' THEN Val END),
           Val2 = MAX(CASE WHEN Type = '2' THEN Val END)
    FROM   Val
    GROUP BY MainID
 ) v ON m.ID = v.MainID;

CodePudding user response：

An elegant way to do it is using pivot:

update main
set val1=[1], val2 = [2] 
from Val
pivot (
    max(val) for type in ([1],[2])  
    ) p
where main.ID=p.MainID