PL/SQL update using loop

Question

I have to update 50 employees' id number. Id numbers are in sequence like

100001, 100002, 100003 ... 100050

I need to update them with another sequence series like

1004001, 1004002, 1004003 ... 1004050

So, I want to update using the loop. But I do not understand how to do this. I am using PL/SQL Developer with Oracle.

However, what I was trying is:

DECLARE 
  i number(1); 
  j number(1); 
BEGIN 
  << outer_loop >> 
  FOR i IN 100001..100051 LOOP             

    << inner_loop >>      
    FOR j IN 1..3 LOOP 
      update employees e
      set    e.IDENTIFICATION_NUMBER = j
      where  e.IDENTIFICATION_NUMBER = i;
    END loop inner_loop;          

  END loop outer_loop; 
END; 
/

Looks like a simple: update employee set IDENTIFICATION_NUMBER = IDENTIFICATION_NUMBER + 904000? — user330315
– user330315, Commented Jan 31, 2018 at 10:20

praveen muppala · Accepted Answer · 2018-01-31 16:44:41Z

3

Are you trying to do something like this to update the value of i with j

 DECLARE 
      i number(1); 
      j number(1); 
    BEGIN 
      j:= 1004001;
      << outer_loop >> 
      FOR i IN 100001..100051 LOOP             

          update employees e
          set    e.IDENTIFICATION_NUMBER = j
          where  e.IDENTIFICATION_NUMBER = i;
          j:=j+1;       

      END loop outer_loop; 
    END; 
    /

edited Jan 31, 2018 at 16:44

answered Jan 31, 2018 at 14:44

praveen muppala

1777 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

StreetCoder Over a year ago

it's helpful though. Just need to small correction, j:= 1004001; should be before loop FOR

eifla001 · Accepted Answer · 2018-01-31 01:15:18Z

do you want to replace the first 3 characters of employee id number, (e.g. 100 from 100001) with 1004, then you can do it by running an update statement,

UPDATE employees
   SET identification_number = '1004'||SUBSTR(identification_number,4)
 WHERE identification_number BETWEEN 100001 AND 100050;

if you want to do it using LOOP

BEGIN
    FOR emp_l IN (SELECT identification_number
                    FROM employees
                   WHERE identification_number BETWEEN 100001 AND 100050)
    LOOP
        UPDATE employees
           SET identification_number = '1004'||SUBSTR(emp_l.identification_number,4)
         WHERE identification_number = emp_l.identification_number;
    END LOOP;
END;
/

OR

BEGIN
    FOR emp_id IN 100001..100050
    LOOP
        UPDATE employees
           SET identification_number = '1004'||SUBSTR(emp_id,4)
         WHERE identification_number = emp_id;
    END LOOP;
END;
/

Collectives™ on Stack Overflow

PL/SQL update using loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related