MERGE для таргетинга столбцов с использованием исходных строк?

Question

У меня есть некоторые хорошо структурированные данные, которые выглядят так:

CREATE TABLE SourceBodyPartColors
(
 person_ID INTEGER NOT NULL, 
 body_part_name VARCHAR(5) NOT NULL
    CHECK (body_part_name IN ('hair', 'eye', 'teeth')), 
 color VARCHAR(20) NOT NULL, 
 UNIQUE (color, body_part_name, person_ID)
);

INSERT INTO SourceBodyPartColors (person_ID, body_part_name, color)
   VALUES (1, 'eye', 'blue'), 
          (1, 'hair', 'blond'), 
          (1, 'teeth', 'white'), 
          (2, 'hair', 'white'), 
          (2, 'teeth', 'yellow'), 
          (3, 'hair', 'red');

К сожалению, целевая структура не так хороша и выглядит примерно так:

CREATE TABLE TargetBodyPartColors
(
 person_ID INTEGER NOT NULL UNIQUE, 
 eye_color VARCHAR(20), 
 hair_color VARCHAR(20), 
 teeth_color VARCHAR(20)
);

INSERT INTO TargetBodyPartColors (person_ID)
   VALUES (1), (2), (3);

Я могу написать SQL-92 UPDATE как это:

UPDATE TargetBodyPartColors
   SET eye_color = (
                    SELECT S1.color
                      FROM SourceBodyPartColors AS S1
                     WHERE S1.person_ID 
                              = TargetBodyPartColors.person_ID
                           AND S1.body_part_name = 'eye'
                   ), 
       hair_color = (
                     SELECT S1.color
                       FROM SourceBodyPartColors AS S1
                      WHERE S1.person_ID 
                               = TargetBodyPartColors.person_ID
                            AND S1.body_part_name = 'hair'
                    ), 
       teeth_color = (
                      SELECT S1.color
                        FROM SourceBodyPartColors AS S1
                       WHERE S1.person_ID 
                                = TargetBodyPartColors.person_ID
                             AND S1.body_part_name = 'teeth'
                     );

... но повторный код беспокоит меня.

Хороший канидат для упрощения использования MERGEПодумал я, но не могу придумать ничего разумного.

Любые идеи, как использовать MERGE с этими данными. (Примечание: я хочу избежать проприетарного UPDATE..FROM syntax, Спасибо.)

Answer 1

WITH Pivoted AS
(
  SELECT person_ID, eye, hair, teeth
  FROM SourceBodyPartColors
    PIVOT
    (
    MAX (color) FOR body_part_name IN ( [eye], [hair], [teeth] )
    ) AS pvt
  )  
MERGE TargetBodyPartColors AS target
USING  Pivoted AS source
ON (target.person_ID = source.person_ID)
WHEN MATCHED THEN 
UPDATE SET eye_color = source.eye,  
           hair_color = source.hair,  
           teeth_color = source.teeth ;