Convert working SELECT query into UPDATE query

Question

In SSMS 2014 I have a working SELECT query and want to convert it to and UPDATE statement to update a field in the main table. I have tried and tried many solutions, each results in a different error.

I've managed to do simple UPDATE SET WHERE queries before, but this is a little more complex using derived tables in the WHERE clause. On Searching I found a JOIN solution but for me it does not like the RIGHT or JOIN and in that solution the SET was the last line and SSMS does not like that either so I getting a little confused now.

This is my working SELECT Statement that selects the correct SKU's:

SELECT        
    dbo.tblHKProducts.SKU_ID, dbo.tblHKProducts.SKU_Description, 
    dbo.tblHKProducts.SKU_Scan_Group
FROM
    dbo.tblHKProducts 
RIGHT OUTER JOIN
    (SELECT        
         tblHKProducts_1.SKU_ID, tblHKProducts_1.SKU_Description, 
         dbo.ForceScanByDescriptions.FindDescription
     FROM            
         dbo.tblHKProducts AS tblHKProducts_1 
     RIGHT OUTER JOIN
         dbo.ForceScanByDescriptions ON tblHKProducts_1.SKU_Description = dbo.ForceScanByDescriptions.FindDescription
     WHERE        
         (tblHKProducts_1.SKU_Description IS NOT NULL)
    ) AS derivedtbl_1 ON dbo.tblHKProducts.SKU_ID = derivedtbl_1.SKU_ID

I'm trying to do something like this, SKU_Scan_Group is a field in tblHKProducts:

UPDATE dbo.tblHKProducts
SET SKU_Scan_Group = N'Yes'
RIGHT OUTER JOIN
    (SELECT        
         tblHKProducts_1.SKU_ID, tblHKProducts_1.SKU_Description, 
         dbo.ForceScanByDescriptions.FindDescription
     FROM            
         dbo.tblHKProducts AS tblHKProducts_1 
     RIGHT OUTER JOIN
         dbo.ForceScanByDescriptions ON tblHKProducts_1.SKU_Description = dbo.ForceScanByDescriptions.FindDescription
     WHERE        
         (tblHKProducts_1.SKU_Description IS NOT NULL)
     ) AS derivedtbl_1 ON dbo.tblHKProducts.SKU_ID = derivedtbl_1.SKU_ID

Any help would be appreciated!

Thanks Kev

Answer 1

This is quite simple, actually. This should work:

UPDATE P
SET P.SKU_Scan_Group = N'Yes'
-- SELECT P.SKU_ID, P.SKU_Description, P.SKU_Scan_Group
FROM dbo.tblHKProducts AS P
RIGHT JOIN (
    SELECT P1.SKU_ID
        , P1.SKU_Description
        , FSBD.FindDescription
    FROM dbo.tblHKProducts AS P1
    RIGHT JOIN dbo.ForceScanByDescriptions AS FSBD
        ON P1.SKU_Description = FSBD.FindDescription
    WHERE P1.SKU_Description IS NOT NULL
    ) AS derivedtbl_1
    ON P.SKU_ID = derivedtbl_1.SKU_ID;

P.S. Start using aliases to reference tables your code might become quite hard to maintain later.