CODEWITHSUNDEEP
sqldatabaseoracle-database
Lucian Sieberhagen
Lucian Sieberhagen

Reputation: 25

ORACLE(TOAD) CASE with multiple conditions for different updates

I need to update qualifications in a Oracle DB, I am running into a problem where my script errors. I would usually create a few smaller update statements to get the job done.

However I thought it would be better to do one query, this is simple but my background is mostly on T-SQL and mySQL not Oracle.

So help would be appreciated. My Statement.

--ALTER SESSION TO CHANGE DT--
alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';

--Update
Update Qualifications_t
Set (COMMENTS = 'Task'),
    (Expiry_DTS = CASE Expiry_DTS
    When cd = '1'
    Then Expiry_DTS = '31/12/2016 23:59:00'
    When cd = '2'
    Then Expiry_DTS = '01/07/2019 23:59:00'
    When cd = '3'
    Then Expiry_DTS = '31/12/1999 23:59:00'
    When cd = '4'
    Then Expiry_DTS = '31/08/2021 23:59:00'
    When cd = '5'
    Then Expiry_DTS = '17/06/2021 23:59:00')
END
Where EXPIRY_DTS IS NULL;

--SELECT
Select * 
from QUALIFICATIONS_T
where COMMENTS = 'Task';

Error at line 5 ORA-00905: missing keyword Yes, I googled it but couldn't figure it out.

Upvotes: 0

Views: 606

Answers (1)

user5683823
user5683823

Reputation:

Remove the parentheses around the update assignments.

Then: it's not clear what you mean by the case expression. Perhaps this:

update qualifications_t
set    comments   = 'task',
       expiry_dts = case when cd = '1' then to_date('31/12/2016 23:59:00',
                                                    'dd/mm/yyyy hh24:mi:ss')
                         when cd = '2' then to_date(....)
                                  (etc.)
                    end
where  expiry_dts is null
;

Notice the structure of a case expression. The name of the column you are updating doesn't belong after the keyword case, and the case expression "returns" values directly, not through assignments. There should be only one assignment ("equal sign"); the case expression is evaluated and returns a single value, used for update.

Note also the proper way to represent date values (assuming the column data type is date, as it should be; if it isn't, you should fix that first).

Upvotes: 2

Related Questions