Prev: reThink Migration � It�s Time for Change!
Next: hello,everyone.I'm a freshman.
From: Thomas Gagne on 2 Jun 2010 22:14
I have an update statement that if run by itself updates 14 rows, but
when I test the value of SQL%ROWCOUNT it's value is 28.

I'm wondering if SQL%ROWCOUNT is counting both the rows found in a
subquery as well as the rows updated by the subquery.

Basically

update tablex
set (col1, col2, col3) = (select /* correlated subquery */ )
where tablex.id in (select /* 14 ids from tablex */ )

14 rows updated

(but SQL%ROWCOUNT = 28!)

--
Visit<http://it.toolbox.com/blogs/anything-worth-doing/>
for more great reading.

From: Vladimir M. Zakharychev on 3 Jun 2010 09:04
On Jun 3, 6:14 am, Thomas Gagne <TandGandGA...(a)gmail.com> wrote:
> I have an update statement that if run by itself updates 14 rows, but
> when I test the value of SQL%ROWCOUNT it's value is 28.
>
> I'm wondering if SQL%ROWCOUNT is counting both the rows found in a
> subquery as well as the rows updated by the subquery.
>
> Basically
>
> update tablex
>        set (col1, col2, col3) = (select /* correlated subquery */ )
>    where tablex.id in (select /* 14 ids from tablex */ )
>
> 14 rows updated
>
> (but SQL%ROWCOUNT = 28!)
>
> --
> Visit<http://it.toolbox.com/blogs/anything-worth-doing/>
> for more great reading.

Can you post a test case (table DDL, sample data, the PL/SQL you're
running?) I could not reproduce this on 10.2.0.4 - SQL%ROWCOUNT equals
the number of rows affected by the update - but maybe my test case is
not representative. Here's what I tried:

create table tablex (id number, col1 number, col2 number, col3
number);

insert into tablex
select level, 1, 1, 1 from sys.dual
connect by level < 21;

declare
cnt number;
begin
update tablex x1
set (col1, col2, col3) =
(select col1+1, col2+1, col3+1
from tablex x2
where x2.id = x1.id)
where id in (select id from tablex where id < 15);
cnt := sql%rowcount;
dbms_output.put_line(cnt);
end;

This updates 14 rows and sql%rowcount is also 14.

Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com

From: Mark D Powell on 3 Jun 2010 11:51
On Jun 2, 10:14 pm, Thomas Gagne <TandGandGA...(a)gmail.com> wrote:
> I have an update statement that if run by itself updates 14 rows, but
> when I test the value of SQL%ROWCOUNT it's value is 28.
>
> I'm wondering if SQL%ROWCOUNT is counting both the rows found in a
> subquery as well as the rows updated by the subquery.
>
> Basically
>
> update tablex
>        set (col1, col2, col3) = (select /* correlated subquery */ )
>    where tablex.id in (select /* 14 ids from tablex */ )
>
> 14 rows updated
>
> (but SQL%ROWCOUNT = 28!)
>
> --
> Visit<http://it.toolbox.com/blogs/anything-worth-doing/>
> for more great reading.

The Oracle version would be important for someone to try to duplicate
the issue. You also needs to post the DDL, data inserts, and code
that duplicates the issue.

Is the tablex.id unique?

HTH -- Mark D Powell --
From: Thomas Gagne on 3 Jun 2010 20:55
Alas, it is one of those stupid things where I haven't permission to
post the code, and recreating it using generic code was more effort than
a work-around so I could keep moving forward.

I'll re-visit this problem soon, as I'm now doing more Oracle than not.
 | 
Pages: 1
Prev: reThink Migration � It�s Time for Change!
Next: hello,everyone.I'm a freshman.