Oracle SQL Update multiple parameter

user_16750157 0
Viewed 69

I have a table which I'm trying to update the cells of based on multiple where clauses, but it's failing and I don't understand why. Can anyone look at this and help?

UserID Options Sub_option Parameters
1 Name Firstname Rick
1 Name Lastname James
1 Mail Autoreply 0
1 Mail Autosignature 0
2 Name Firstname Rick
2 Name Lastname Astley
2 Mail Autoreply 0
2 Mail Autosignature 1 
UPDATE Our_users
   SET Parameters = CASE
       WHEN UserID = '2' AND Options = 'MAIL' AND Sub_option = 'Autoreply' THEN '1'
       WHEN UserID = '2' AND Options = 'MAIL' AND Sub_option = 'Autosignature' THEN '0'
END

When running this, it seems to update "Parameters" for all rows. Can't I use "AND" in WHEN clause?

sql-update oracle sql
2 Answers

Add a WHERE clause to restrict the update operations to the specific subset you want.

For example:

UPDATE Our_users
   SET Parameters = CASE
       WHEN UserID = '2' AND Options = 'MAIL' AND Sub_option = 'Autoreply' THEN '1'
       WHEN UserID = '2' AND Options = 'MAIL' AND Sub_option = 'Autosignature' THEN '0'
   END
WHERE UserID = '2' AND Options = 'MAIL' AND Sub_option = 'Autoreply' 
   or UserID = '2' AND Options = 'MAIL' AND Sub_option = 'Autosignature'
user_64361910

This is because you did not use ELSE for other rows which do not match which your CASE:

UPDATE Our_users
   SET Parameters = CASE
       WHEN UserID = '2' AND Options = 'MAIL' AND Sub_option = 'Autoreply' THEN '1'
       WHEN UserID = '2' AND Options = 'MAIL' AND Sub_option = 'Autosignature' THEN '0'
       ELSE Parameters
   END

This query works, but if you have too many data in your table it takes too much time with ELSE. It means you should filter your rows with where:

UPDATE Our_users
   SET Parameters = CASE
       WHEN UserID = '2' AND Options = 'MAIL' AND Sub_option = 'Autoreply' THEN '1'
       WHEN UserID = '2' AND Options = 'MAIL' AND Sub_option = 'Autosignature' THEN '0'
   END
WHERE UserID = '2' AND Options = 'MAIL' AND Sub_option = 'Autoreply' 
   OR UserID = '2' AND Options = 'MAIL' AND Sub_option = 'Autosignature'
user_148618230
Related