Need help with the Merge statement

sql, sql-server, sql-server-2008, t-sql

Solution

I believe you have to alias your your source data like so:

USING (select SOR.identity_column, 
   CASE  WHEN left(SOR.FPARTNO, 2) = 'BL' 
   THEN 'Blue'        
   WHEN left(SOR.FPARTNO, 2) = 'RD' 
   THEN 'Red'        
   ELSE 'White'      
   END from Sorels AS SOR) **AS SOR** ON  (SORe.fkey_id = SOR.identity_column)

Problem

I want to update a table called Sorels_ext from a table called Sorels. The link between them is the fkey_id of Sorels_ext equals the identity_column of the Sorels table. This is my first attempt at a Merge statement and I'm trying to learn the syntax. ``` MERGE Sorels_ext AS SORe USING (select SOR.identity_column, CASE WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue' WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red' ELSE 'White' END from Sorels AS SOR) ON (SORe.fkey_id = SOR.identity_column) WHEN MATCHED THEN UPDATE SET SORe.fkey_id = SOR.identity_column, SORe.Color = select SOR.identity_column, CASE WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue' WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red' ELSE 'White' END WHEN NOT MATCHED THEN INSERT (SORe.fkey_id, SORe.Color) VALUES (SOR.identity_column, SORe.Color = select SOR.identity_column, CASE WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue' WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red' ELSE 'White' END); ``` When I run this, I get the following error: Error 10/22/2009 1:38:51 PM 0:00:00.000 SQL Server Database Error: Incorrect syntax near the keyword 'ON'. 46 0 *** ADDED INFO ****** After the first fix suggested, the code is as follows: ``` MERGE Sorels_ext AS SORe USING (select SOR.identity_column, CASE WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue' WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red' ELSE 'White' END from Sorels) AS SOR ON (SORe.fkey_id = SOR.identity_column) WHEN MATCHED THEN UPDATE SET SORe.fkey_id = SOR.identity_column, SORe.Color = CASE WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue' WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red' ELSE 'White' END WHEN NOT MATCHED THEN INSERT (SORe.fkey_id, SORe.Color) VALUES (SOR.identity_column, CASE WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue' WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red' ELSE 'White' END); ``` Now I get the following error: Error 10/22/2009 2:03:29 PM 0:00:00.000 SQL Server Database Error: The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead. 55 0 ******* ADDED MORE INFO ****** After adjustments from suggestions, I have the following: ``` MERGE Sorels_ext AS SORe USING (select SOR1.identity_column, CASE WHEN left(SOR1.FPARTNO, 2) = 'BL' THEN 'Blue' WHEN left(SOR1.FPARTNO, 2) = 'RD' THEN 'Red' ELSE 'White' END as colors from Sorels as SOR1 ) as SOR ON (SORe.fkey_id = SOR.identity_column) WHEN MATCHED THEN UPDATE SET SORe.fkey_id = SOR.identity_column, SORe.Color = CASE WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue' WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red' ELSE 'White' END WHEN NOT MATCHED THEN INSERT (fkey_id, Color) VALUES (SOR.identity_column, CASE WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue' WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red' ELSE 'White' END); ``` I get the error: Error 10/22/2009 2:46:51 PM 0:00:00.000 SQL Server Database Error: Invalid column name 'FPARTNO'. 56 0 What am I doing wrong? **** I GOT IT!!! ***** ``` MERGE Sorels_ext AS SORe USING (select SOR.identity_column, CASE WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue' WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red' ELSE 'White' END as colors from Sorels as SOR) SOR1 ON (SORe.fkey_id = SOR1.identity_column) WHEN MATCHED THEN UPDATE SET SORe.fkey_id = SOR1.identity_column, SORe.Color = SOR1.colors WHEN NOT MATCHED THEN INSERT (fkey_id, Color) VALUES (SOR1.identity_column, SOR1.colors); ```

Original source