Update Select Case Statement in SQL Server:

Question

I have written the below update statement to update the StatusID of the PLN_DU_Vendor table.

UPDATE 
PLN_DU_Vendor
SET
    PLN_DU_Vendor.StatusID = 2
FROM
    PLN_DU_Vendor 
INNER JOIN PMT_RFDDetail D ON PLN_DU_Vendor.DUID = D.DUID
INNER JOIN PMT_RFDHeader H ON H.RFDID = D.RFDID AND PLN_DU_Vendor.VendorID = H.VendorID
INNER JOIN PLN_LASCO_Header LH ON LH.LASCOID = H.LASCOID AND LH.ReleaseID = PLN_DU_Vendor.ReleaseID
AND PLN_DU_Vendor.VendorDUQuantity = (D.DespatchQuantity + ISNULL(PLN_DU_Vendor.RFDQuantity, 0))
WHERE
     H.RFDID = 10

If the condition

PLN_DU_Vendor.VendorDUQuantity = (D.DespatchQuantity + ISNULL(PLN_DU_Vendor.RFDQuantity, 0))

fails, I need to update the StatusID as 1. How to do this in the same update select statement.

Solution

Looks like you'll want to move the condition from the JOIN to the assignment as a CASE statement:

UPDATE 
PLN_DU_Vendor
SET
    PLN_DU_Vendor.StatusID = CASE 
    WHEN PLN_DU_Vendor.VendorDUQuantity = (D.DespatchQuantity + ISNULL(PLN_DU_Vendor.RFDQuantity, 0))
    THEN 2
    ELSE 1
    END
FROM
    PLN_DU_Vendor 
INNER JOIN PMT_RFDDetail D ON PLN_DU_Vendor.DUID = D.DUID
INNER JOIN PMT_RFDHeader H ON H.RFDID = D.RFDID AND PLN_DU_Vendor.VendorID = H.VendorID
INNER JOIN PLN_LASCO_Header LH ON LH.LASCOID = H.LASCOID AND LH.ReleaseID = PLN_DU_Vendor.ReleaseID
WHERE
     H.RFDID = 10