Home » SQL & PL/SQL » SQL & PL/SQL » FOR UPDATE
FOR UPDATE [message #275724] Mon, 22 October 2007 09:04 Go to next message
raji.s
Messages: 52
Registered: February 2005
Member
The below two versions of pl/sql blk ahs only 1 difference. The second one updates the rows but the first one doesn't. The only difference in both of them is "for update" and "for update of <colname>" .

Can someone plz explain why is such behavior.

set serverout on

declare
cursor c1 is select e.empno, e.ename, d.deptno from emp e , dept d where e.deptno=d.deptno for update;
begin
for r1 in c1 loop
update dept set deptno=r1.deptno where current of c1;
dbms_output.put_line(sql%rowcount);
end loop;
rollback;
end;
/


declare
cursor c1 is select e.empno, e.ename, d.deptno from emp e , dept d where e.deptno=d.deptno for update of dname;
begin
for r1 in c1 loop
update dept set deptno=r1.deptno where current of c1;
dbms_output.put_line(sql%rowcount);
end loop;
rollback;
end;
/
Re: FOR UPDATE [message #275726 is a reply to message #275724] Mon, 22 October 2007 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Can someone plz explain why is such behavior.

Which behaviour? Developer one?

Why not just an update?

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: FOR UPDATE [message #275739 is a reply to message #275724] Mon, 22 October 2007 09:52 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
Hi everyone,

Let me be specific about my question.

Why the first pl/sql block is not updating any rows .

Thanks.
Re: FOR UPDATE [message #275749 is a reply to message #275739] Mon, 22 October 2007 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prove it.

Regards
Michel
Re: FOR UPDATE [message #275760 is a reply to message #275724] Mon, 22 October 2007 10:38 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
Hi All,

Attached file contains source data view of emp and dept table.

O/P OF FIRST PL/SQL BLOCK.
SQL>  declare
  2    cursor c1 is select e.empno, e.ename, d.deptno from emp e , dept d where e.deptno=d.deptno for update;
  3     begin
  4      for r1 in c1 loop
  5       update dept set deptno=r1.deptno where current of c1;
  6     dbms_output.put_line(sql%rowcount);
  7      end loop;
  8      rollback;
  9     end;
 10   /
0
0
0
0
0
0
0
0
0
0
0
0
0
0

PL/SQL procedure successfully completed.

O/P OF SECOND PL/SQL BLOCK .

SQL> declare
  2    cursor c1 is select e.empno, e.ename, d.deptno from emp e , dept d where e.deptno=d.deptno for update of dname;
  3     begin
  4      for r1 in c1 loop
  5       update dept set deptno=r1.deptno where current of c1;
  6     dbms_output.put_line(sql%rowcount);
  7      end loop;
  8      rollback;
  9     end;
 10   /
1
1
1
1
1
1
1
1
1
1
1
1
1
1

PL/SQL procedure successfully completed.


[Mod-edit: Added code-tags]

[Updated on: Mon, 22 October 2007 12:32] by Moderator

Report message to a moderator

Re: FOR UPDATE [message #275774 is a reply to message #275724] Mon, 22 October 2007 11:21 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
Hi Michel,

I hope i have proved my point .

Can someone please justify why the code is behaving differenly?
Re: FOR UPDATE [message #275794 is a reply to message #275724] Mon, 22 October 2007 13:21 Go to previous messageGo to next message
raji.s
Messages: 52
Registered: February 2005
Member
Hi,

I tried hard to find the reason why the code is behaving such but no success.

Request the senior GURUS to have a look and answer if possible.

Regards.
Re: FOR UPDATE [message #275810 is a reply to message #275794] Mon, 22 October 2007 14:57 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
When there are two (or more) tables joined in the SELECT ... FOR UPDATE statement, you need to specify a column which is to be updated.

This subject has been discussed recently, so - you might try to find it using the Search facility of the OraFAQ Forum.
Previous Topic: From SQL server
Next Topic: query required...
Goto Forum:
  


Current Time: Sun Dec 11 07:51:47 CST 2016

Total time taken to generate the page: 0.06996 seconds