UPDATE with multiple criteria and multiple rows

Question

I would like to be able to update a table using one UPDATE statement, but include various criteria in it. The following code contains a table test_table which holds main_id which can have product_case id's and customer_case id's. I would like to be able to check the table CASETABLE and if theres a 'PC' case or a 'CC' case associated with main_id then update the appropriate fields.

UPDATE 
    test_table tt
SET 
    tt.product_case = CASE WHEN ct.TYPE = 'PC' THEN ct.ID ELSE tt_.product_case END,
    tt.customer_case = CASE WHEN ct.TYPE = 'CC' THEN ct.ID ELSE tt.customer_case END
FROM 
    CASETABLE ct 
WHERE 
    ct.STATUS = 'ACTIVE'
    AND ct.MAINRECORD = tt.main_id

Obviously this doesn't work if the main WHERE returns 2 rows as each case will try to SET the correct value and then SET its original value again causing a SQL ERROR 21506 (trying to set more than once for same row). How can I do this without just using two separate UPDATE statements?

Answer 1

Please try this

UPDATE 
   tt
SET 
    tt.product_case = CASE WHEN ct.TYPE = 'PC' THEN ct.ID ELSE tt_.product_case END,
    tt.customer_case = CASE WHEN ct.TYPE = 'CC' THEN ct.ID ELSE tt.customer_case END
FROM 
    test_table tt
    INNER JOIN CASETABLE ct 
    ON ct.MAINRECORD = tt.main_id
WHERE 
    ct.STATUS = 'ACTIVE'