Search code examples
sqlsql-update

SQL Update Case when - ORA00905

I searched all of the recommended or earlier solved questions, when I try to apply some of their logics, I can not get it to work. I have the following statement:

Update TableABC
Set ColA = CASE WHEN ColA IS NULL THEN 'Not found' ELSE ColA END,
    ColB = CASE WHEN ColB IS NULL THEN 'Not found' ELSE ColB END,
    ColC = CASE WHEN ColC IS NULL THEN 'Not found' ELSE ColC END,
    ColD = CASE WHEN ColD IS NULL THEN 'Not found' ELSE ColD END
Where Status = 'Completed'

I also tried with the SET statement in each update, but that didnt help either...

Update TableABC
Set ColA = CASE WHEN ColA IS NULL THEN 'Not found' ELSE ColA END,
Set ColB = CASE WHEN ColB IS NULL THEN 'Not found' ELSE ColB END,
Set ColC = CASE WHEN ColC IS NULL THEN 'Not found' ELSE ColC END,
Set ColD = CASE WHEN ColD IS NULL THEN 'Not found' ELSE ColD END
Where Status = 'Completed'

This returns with ORA 00905 Missing Keyword From my perspective this looks valid statement, where is the Error? Could someone explain me?

Solution

  • I solved it with suggestion from Stu in comments by using Coalesce instead of CASE. Code below:

    Update TableABC
Set ColA = COALESCE(ColA, n'Not found'),
    ColB = COALESCE(ColB, n'Not found'),
    ColC = COALESCE(ColC, n'Not found'),
    ColD = COALESCE(ColD, n'Not found')
Where Status = 'Completed'