Trying to Update Case When Then, based on matching IDs in two tables

I am trying to updated IDs in a table named Price, based on matches of IDs in a table named CW. Here is the SQL that I’m testing.

UPDATE Price
SET    ISN = (case when CW.id_cd = 'ISN' THEN CW.id_number else ISN end),
       SED = (case when CW.id_cd = 'SED' THEN CW.id_number else SED end),
       CSP = (case when CW.id_cd = 'CSP' THEN CW.id_number else CSP end)
FROM   CW
WHERE  Price.ID_GLOBAL = CW.asset_id

Here is a screen shot of the two schemas. So, now the values in the yellow cells are missing IDs, but I’m trying to get them filled in with the values I’m showing here.

The weird thing is that most of the IDs in the Price table are updated fine, based on the same IDs in the CW table, but not all IDs are being updated. After doing the above update, when I run the SQL below, I get a bunch of records returned, but I would expect no records at all because all CSP (IDs) should have been updated in the Price table.

Select *
From Price
INNER JOIN CW
ON Price.id_global = CW.asset_id
Where Price.CSP IS NUll
AND CW.id_cd = 'CSP'

Answer

I think you have multiple rows in CW for each ID_GLOBAL. The problem is that only one matching row gets used. The solution is either a series of LEFT JOINs or pre-aggregation:

UPDATE Price
    SET ISN = COALESCE(cw.isn, price.isn),
        SED = COALESCE(cw.sed, price.sed),
        CSP = COALESCE(cw.csp, price.csp)
FROM (SELECT asset_id,
             MAX(case when CW.id_cd = 'ISN' THEN CW.id_number END) as ISN,
             MAX(case when CW.id_cd = 'SED' THEN CW.id_number END) as SED,
             MAX(case when CW.id_cd = 'CSP' THEN CW.id_number END) as CSP
      FROM CW
      GROUP BY asset_id
     ) cw
WHERE Price.ID_GLOBAL = CW.asset_id;

Note: This assumes that the new values are not ever NULL. If that is a possibility, the assignment logic needs to take that into account. I am guessing that complication is unnecessary.

EDIT:

Do this in three steps. For 'ISN':

UPDATE Price
    SET ISN = cw.isn
FROM CW
WHERE Price.ID_GLOBAL = CW.asset_id AND
      CW.id_cd = 'ISN';

I am guessing the COALESCE() is not necessary.