| update records using cursor (merged 3) [message #385559] |
Tue, 10 February 2009 02:30  |
nipa.pillai
Messages: 11
Registered: February 2009
|
Junior Member |
|
|
This is the code i have written.Though the code compiles successfully the value of the commission is not getting updated in the emp table.. please help
declare
cursor cur
is
SELECT e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.deptno,
e.comm,
d.dname
FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO for update;
BEGIN
for rec in cur
loop
if rec.dname='SALES' then
update emp set comm=0 where current of cur;
dbms_output.put_line('commission updated');
end if ;
end loop;
END;
[EDITED by LF: [color] tags substituted with [code] tags]
[Updated on: Tue, 10 February 2009 15:19] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: update records using cursor [message #385573 is a reply to message #385559] |
Tue, 10 February 2009 02:47  |
icmohsin
Messages: 12
Registered: January 2009
Location: Mumbai
|
Junior Member |
|
|
Try this out
declare
cursor cur
is
SELECT e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.deptno,
e.comm,
d.dname
FROM tt e,DEPT d WHERE e.DEPTNO=d.DEPTNO for update;
BEGIN
for rec in cur
loop
if rec.dname='SALES' then
update tt set comm=0 where DEPTNO = REC.DEPTNO; dbms_output.put_line('commission updated');
end if ;
end loop;
END;
|
|
|
|
| Re: update records using cursor [message #385574 is a reply to message #385563] |
Tue, 10 February 2009 02:48 |
nipa.pillai
Messages: 11
Registered: February 2009
|
Junior Member |
|
|
|
the problem is am using two tables and i have to write a pl/sql block.So for that i used cursor. Though am getting messages that commission has been updated its not actually getting updated in the emp table.
|
|
|
|
|
|
| Re: update records using cursor [message #385576 is a reply to message #385574] |
Tue, 10 February 2009 02:54 |
nipa.pillai
Messages: 11
Registered: February 2009
|
Junior Member |
|
|
Formated code
declare
cursor cur
is
SELECT e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.deptno,
e.comm,
d.dname
FROM EMP e,DEPT d WHERE e.DEPTNO=d.DEPTNO for update;
BEGIN
for rec in cur
loop
if rec.dname='SALES' then
update emp set comm=0 where current of cur;
dbms_output.put_line('commission updated');
end if ;
end loop;
END; |
|
|
|
| Re: update records using cursor [message #385578 is a reply to message #385574] |
Tue, 10 February 2009 03:01 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | the problem is am using two tables and i have to write a pl/sql block.
|
Why if you can do it in a single SQL statement?
For instance:
update emp set comm=0
where deptno = (select deptno from dept where dname='SALES')
/
Regards
Michel
[Updated on: Tue, 10 February 2009 03:01] Report message to a moderator |
|
|
|
|
|
|
|
| Re: update records using cursor [message #385586 is a reply to message #385578] |
Tue, 10 February 2009 03:22 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
That = probably wants to be an IN:update emp set comm=0
where deptno IN (select deptno from dept where dname='SALES')
/
There is absolutely no advantage to doing this in an anonymous Pl/Sql block.
If for some reason (say because your teacher had told you to do it that way) you still wanted to do it in a pl/sql block, then the pl/sql to write would be:BEGIN
update emp
set comm=0
where deptno IN (select deptno
from dept
where dname='SALES');
END;
/
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
