sql - MERGE to target columns using source rows? -

i have nicely-structured data looks this:

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 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'); 

sadly, target structure no nice, , looks more this:

create table targetbodypartcolors (  person_id integer not null unique,   eye_color varchar(20),   hair_color varchar(20),   teeth_color varchar(20) );  insert targetbodypartcolors (person_id)    values (1), (2), (3); 

i can write sql-92 update this:

update targetbodypartcolors    set eye_color = (                     select s1.color                       sourcebodypartcolors s1                      s1.person_id                                = targetbodypartcolors.person_id                            , s1.body_part_name = 'eye'                    ),         hair_color = (                      select s1.color                        sourcebodypartcolors s1                       s1.person_id                                 = targetbodypartcolors.person_id                             , s1.body_part_name = 'hair'                     ),         teeth_color = (                       select s1.color                         sourcebodypartcolors s1                        s1.person_id                                  = targetbodypartcolors.person_id                              , s1.body_part_name = 'teeth'                      ); 

...but repeated code bothers me.

a canidate simplifying using merge, thought, can't come reasonable.

any ideas how use merge data. (note: want avoid proprietary update..from syntax, thanks.)

with pivoted (   select person_id, eye, hair, teeth   sourcebodypartcolors     pivot     (     max (color) body_part_name in ( [eye], [hair], [teeth] )     ) pvt   )   merge targetbodypartcolors target using  pivoted source on (target.person_id = source.person_id) when matched  update set eye_color = source.eye,              hair_color = source.hair,              teeth_color = source.teeth ;