Re: SQL%ROWCOUNT includes counts from subqueries?
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Thu, 3 Jun 2010 08:51:52 -0700 (PDT)
Message-ID: <e2fd19cc-ff64-49fe-92b7-08a08a64bde4_at_q23g2000vba.googlegroups.com>
On Jun 2, 10:14 pm, Thomas Gagne <TandGandGA..._at_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.
Date: Thu, 3 Jun 2010 08:51:52 -0700 (PDT)
Message-ID: <e2fd19cc-ff64-49fe-92b7-08a08a64bde4_at_q23g2000vba.googlegroups.com>
On Jun 2, 10:14 pm, Thomas Gagne <TandGandGA..._at_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 -- Received on Thu Jun 03 2010 - 10:51:52 CDT