CODEWITHSUNDEEP
sqloracle-database
srk786
srk786

Reputation: 549

how to update multiple rows in oracle

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

Upvotes: 8

Views: 58937

Answers (5)

Adam
Adam

Reputation: 2398

Not sure what exactly your db set up is, but for me I solved a similar issue using some simple PLSQL.

Let's say you have two tables:

  1. "request" table with many columns one being a "date" column and its primary key being "id"
  2. "item" table with many columns with one also being a "date" column and another being a pointer to the request it belongs to called "request_id"

And let's say this is for when a user is making a purchase request for one or more items, a one to many relationship, and so the date columns on the items should match that of the request they belong to.

Now let's say the date column on the request has been left blank accidentally but thankfully not the dates of the items. And so we want to updated the request date column to match that of their corresponding items' date value. And so for each request, take one of its items and take the item.date and set the request.date to this item.date using PLSQL:

begin
for iteration_row in
(select distinct r.id as r_id_from_select, i.date as i_date_from_select
  from request r
    join item i on item.request_id = r.id)
loop
  update request r_to_update
  set date = iteration_row.i_date_from_select
  where r_to_update.id = iteration_row.r_id_from_select
end loop;
end;

Another example of PLSQL looping through the select but this one is not updating like I am above: https://stackoverflow.com/a/49635601/2888009

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

For conditional update, you could use multiple update statements, or use CASE expression in the SET clause.

Something like,

UPDATE table
SET schema.column =  CASE
                        WHEN column1= 'value1' AND column2='value2' THEN
                          'Y'
                        ELSE
                          'N'
                     END

I wish you tried to search for a similar question on this site, there was a recent question and this was my answer.

Upvotes: 3

Upendra Hukeri
Upendra Hukeri

Reputation: 23

If you have two tables like:

CREATE TABLE test_tab_1 (id NUMBER, name VARCHAR2(25));

CREATE TABLE test_tab_2 (id NUMBER, name VARCHAR2(25));

You can use UPDATE statement as below:

UPDATE test_tab_1
SET test_tab_1.name = (SELECT test_tab_2.name FROM test_tab_2
WHERE test_tab_1.id = test_tab_2.id);

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727047

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

Upvotes: 10

collapsar
collapsar

Reputation: 17258

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

Upvotes: 4

Related Questions