how to update multiple rows in oracle

Question

I would like to update multiple rows with different values for all different records, but don't have any idea how to do that, i am using below sql to update for single record but i have 200 plus records to update

update employee
set staff_no = 'ab123'
where depno = 1

i have 50 dep and within those dep i need to update 200 plus staff no. any idea. At the moment if i just do a

 select * from Departments 

i can see list of all employee which needs staff no updating.

UPDATE person
   SET staff_no = 
       CASE person_no
            WHEN 112 THEN 'ab123'
            WHEN 223 THEN 'ab324'
            WHEN 2343 THEN 'asb324'
            and so on.....


       END

Answer 1

You should be able to use MERGE statement to do it in a single shot. However, the statement is going to be rather large:

MERGE INTO employee e
USING (
   SELECT 1 as d_id, 'cd234' as staff_no FROM Dual
       UNION ALL
   SELECT 2 as d_id, 'ef345' as staff_no FROM Dual
       UNION ALL
   SELECT 3 as d_id, 'fg456' as staff_no FROM Dual
       UNION ALL
   ... -- More selects go here
   SELECT 200 as d_id, 'za978' as staff_no FROM Dual
) s
ON (e.depno = S.d_id)
WHEN MATCHED THEN UPDATE SET e.staff_no= s.staff_no

Answer 2

use a case expression

UPDATE employee
   SET staff_no = 
           CASE depno
                WHEN 1 THEN 'ab123'
                WHEN 2 THEN 'ab321'
                --...
                ELSE staff_no
           END
 WHERE depno IN ( 1, 2 ) -- list all cases here. use a subquery if you don't want to / cannot enumerate