Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Update (Oracle 9i)
Bulk Update [message #564053] Tue, 21 August 2012 02:50 Go to next message
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

I want to update the names of employees by concatnating with A for DEPT 20.
For that I have writteh the following PL/SQL block.
But instead of one A the ename are concatinating with five AAAAA s.

   TYPE lt_emp_arr IS TABLE OF t_emp.deptno%TYPE;
   l_emp_arr   lt_emp_arr;
       SELECT   deptno
   BULK COLLECT INTO   l_emp_arr
         FROM   emp
        WHERE   deptno = 20;

   FORALL i IN l_emp_arr.FIRST .. l_emp_arr.LAST
      UPDATE   emp
         SET   ename = ename || 'A'
       WHERE   deptno = l_emp_arr (i);


I got the following out put.


7566	JONESAAAAA	2000	20	
7788	SCOTTAAAAA	3000	20	
7902	FORDAAAAA	3000	20	
7369	SMITHAAAAA	8000	20	
7876	ADAMSAAAAA      1300	20

But I want the output like this


7566	JONESA	2000	20	
7788	SCOTTA	3000	20	
7902	FORDA	3000	20	
7369	SMITHA	8000	20	
7876	ADAMSA  1300	20

Please help me to how to achieve this.

Thanks in advance.
Re: Bulk Update [message #564060 is a reply to message #564053] Tue, 21 August 2012 03:08 Go to previous message
Michel Cadot
Messages: 57642
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
(I assume this is a PL/SQL exercise and not a real update case and so a single SQL statement which is possible is out of the question.)

deptno is not a primary key for emp table, so for each employee you find in the query you update all employees of the department => 5 employees = 5 A.

Your SELECT must return the employee number that should be used to then update the employee.

Previous Topic: With Clause
Next Topic: Procedure Explanaton
Goto Forum:

Current Time: Wed Apr 23 18:15:32 CDT 2014

Total time taken to generate the page: 0.08968 seconds