8

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
Improve this question
5
  • for example if depno =1 set staffno = ab123 ...... if depno = 2 set staffno =ab321 Commented Feb 19, 2015 at 14:23
  • Are you saying you need to update 200 records all having different values? Is there a way to compute those values? Commented Feb 19, 2015 at 14:26
  • i have around 150 person_id's all look different, i need to update staff_no against those person id's, so in short 150 person_no's and 150 staff_no's Commented Feb 19, 2015 at 14:43
  • Create a sqlfiddle with some sample DDL and date showing relation if the answers below don't satisfy. Commented Feb 19, 2015 at 14:45
  • @srk786 don't panic. even if there is no test db where you can get comfortable with the technique, nothing willbe altered in your prod db until you COMMIT - try it for 2 small departments first, check the results, issue a ROLLBACK. Commented Feb 19, 2015 at 14:52

5 Answers 5

Reset to default
10

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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Why MERGE for only an update? The update statement is meant for that.
4

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
Improve this answer

5 Comments

thanks for the reply, will it work if i put all the dep no as case and within that case put all staff number, there is no sub query, i just pulled up the department table and than i will put all staff numbers manually
You can cater for all possible depno values in a single case expression. I'd recommend to use the ELSE branch to keep the old value to safeguard against unexpected depno s.
will it be possible if you can please write the sql query/ case statement where i have 150 different person id's and i want to update 150 staff_no against those id's, i will be putting in person_id's and staff number manually as an update within a query
sorry sir, i gladly help people but i won't do your work for you ;-). Even more so as i'm not so sure about your data model.
please have a look at my question. 2nd part i am going to execute that. does it look ok
3

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.

Improve this answer

1 Comment

its really stressing, i need to update on production database, i never used the case statement before, i am going to edit my question with what i am going to use and please if you can help whether i am doing the right way
1

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);
Improve this answer

Comments

0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.