Brandon Pacheco Brandon Pacheco - 2 months ago 15
SQL Question

Updating multiple fields in SQL using WHERE,WHEN,THEN

I need to update multiple fields with certain conditions. I know my syntax is incorrect but I can't figure out what's wrong, this should be easy but it's just not clicking. Please help.

UPDATE WO_TASK_LABOR
SET BILLING_RATE
WHEN SYSUR_AUTO_KEY = 577 THEN 26.51
WHEN SYSUR_AUTO_KEY = 605 THEN 18.71
WHEN SYSUR_AUTO_KEY = 637 THEN 16.84
WHEN SYSUR_AUTO_KEY = 741 THEN 16.50
WHEN SYSUR_AUTO_KEY = 792 THEN 26.00
WHEN SYSUR_AUTO_KEY = 808 THEN 14.99
WHEN SYSUR_AUTO_KEY = 812 THEN 24.51
WHEN SYSUR_AUTO_KEY = 813 THEN 26.00
WHEN SYSUR_AUTO_KEY = 884 THEN 28.00
WHEN SYSUR_AUTO_KEY = 768 THEN 21.00
WHEN SYSUR_AUTO_KEY = 892 THEN 21.50
WHEN SYSUR_AUTO_KEY = 910 THEN 20.50
WHEN SYSUR_AUTO_KEY = 920 THEN 22.00
WHEN SYSUR_AUTO_KEY = 962 THEN 15.00
WHEN SYSUR_AUTO_KEY = 970 THEN 23.00
WHEN SYSUR_AUTO_KEY = 971 THEN 56.36
WHEN SYSUR_AUTO_KEY = 977 THEN 16.00
WHEN SYSUR_AUTO_KEY = 979 THEN 24.00
WHEN SYSUR_AUTO_KEY = 982 THEN 20.00
WHEN SYSUR_AUTO_KEY = 985 THEN 18.00
WHEN SYSUR_AUTO_KEY = 828 THEN 26.50
WHEN SYSUR_AUTO_KEY = 834 THEN 23.00
END

Answer Source

You missed CASE:

UPDATE WO_TASK_LABOR
    SET BILLING_RATE = 
    CASE
    WHEN SYSUR_AUTO_KEY = 577 THEN 26.51
    WHEN SYSUR_AUTO_KEY = 605 THEN 18.71
    WHEN SYSUR_AUTO_KEY = 637 THEN 16.84
    WHEN SYSUR_AUTO_KEY = 741 THEN 16.50
    WHEN SYSUR_AUTO_KEY = 792 THEN 26.00
    WHEN SYSUR_AUTO_KEY = 808 THEN 14.99
    WHEN SYSUR_AUTO_KEY = 812 THEN 24.51
    WHEN SYSUR_AUTO_KEY = 813 THEN 26.00
    WHEN SYSUR_AUTO_KEY = 884 THEN 28.00
    WHEN SYSUR_AUTO_KEY = 768 THEN 21.00
    WHEN SYSUR_AUTO_KEY = 892 THEN 21.50
    WHEN SYSUR_AUTO_KEY = 910 THEN 20.50
    WHEN SYSUR_AUTO_KEY = 920 THEN 22.00
    WHEN SYSUR_AUTO_KEY = 962 THEN 15.00
    WHEN SYSUR_AUTO_KEY = 970 THEN 23.00
    WHEN SYSUR_AUTO_KEY = 971 THEN 56.36
    WHEN SYSUR_AUTO_KEY = 977 THEN 16.00
    WHEN SYSUR_AUTO_KEY = 979 THEN 24.00
    WHEN SYSUR_AUTO_KEY = 982 THEN 20.00
    WHEN SYSUR_AUTO_KEY = 985 THEN 18.00
    WHEN SYSUR_AUTO_KEY = 828 THEN 26.50
    WHEN SYSUR_AUTO_KEY = 834 THEN 23.00
    ELSE 0
    END
WHERE ENTRY_DATE >= '01-JUN-17'