How do I update two variables in a oracle sql server update using case statement-CodePudding

I am trying to update two variables in a Oracle server as follows:

UPDATE UserTable
SET user_email='[email protected]', 
               (CASE WHEN reason != '' THEN why_update= 'change email server' END)
WHERE user_id = 123

I want to update why_update column only if the user provided a reason for update otherwise leave the column as it is (which is varchar type and can be NULL).

Or any other better solution?

CodePudding user response：

If you're trying to update multiple columns, you would need to do it like:

UPDATE UserTable
SET user_email = '[email protected]', 
    why_update = CASE WHEN reason IS NOT NULL THEN 'change email server' END
WHERE user_id = 123;

You would always end up updating both columns - in this case, if reason is null, then why_update would be set to NULL, losing any previous value. If you don't want that to happen, you would need to add in an ELSE why_update clause into the CASE expression.

N.B. I have changed your != '' into IS NOT NULL because an empty string ('') is recognised as being a NULL in Oracle. NULL will never match an equals or not equals check, so your above CASE expression won't work as I suspect you want it to work.

CodePudding user response：

THis will work:

UPDATE UserTable
SET user_email='[email protected]', 
    why_update= CASE WHEN reason != '' THEN 'change email server' else why_update END
WHERE user_id = 123