how to update multiple rows in oracle
oracle, sql
Solution
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
Problem
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 ```