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
ajaykumarkona
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.

DECLARE
   TYPE lt_emp_arr IS TABLE OF t_emp.deptno%TYPE;
   l_emp_arr   lt_emp_arr;
BEGIN
       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);

   COMMIT;
END;
/


I got the following out put.

EMPNO	ENAME	         SAL   DEPTNO

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

EMPNO	ENAME	SAL   DEPTNO

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: 59165
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.

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


Current Time: Mon Sep 22 12:05:58 CDT 2014

Total time taken to generate the page: 0.14750 seconds