Home » SQL & PL/SQL » SQL & PL/SQL » sql%rowcount difference in two update statements. (Oracle9i Enterprise Edition Release, 9.2.0.5.0, Linux)
sql%rowcount difference in two update statements. [message #379914] Thu, 08 January 2009 06:49 Go to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

Hi,
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

I have emp and emp_ram table with data as follows
SQL> select empno,ename,hiredate,deptno
  2  from   emp
  3  where  empno in (7839,7654,7876);

     EMPNO ENAME      HIREDATE      DEPTNO
---------- ---------- --------- ----------
      7876 ADAMS      12-JAN-83         20
      7654 MARTIN     28-SEP-81         30
      7839 Shajahan S 17-NOV-81         10

SQL> select * from emp_ram;

     EMPNO ENAME      HIREDATE      DEPTNO
---------- ---------- --------- ----------
      7876            12-JAN-83         10
      7654            28-SEP-81         10
      7839            17-NOV-81         10

I am trying to update emp_ram from emp
SQL> UPDATE emp_ram a
  2     SET ( ename, hiredate ) =
  3            ( SELECT ename, TRUNC ( SYSDATE )
  4             FROM   emp b
  5             WHERE  a.empno = b.empno
  6             AND    a.deptno <> b.deptno );

3 rows updated.

SQL> select * from emp_ram;

     EMPNO ENAME      HIREDATE      DEPTNO
---------- ---------- --------- ----------
      7876 ADAMS      08-JAN-09         10
      7654 MARTIN     08-JAN-09         10
      7839                              10

SQL> rollback;

Rollback complete.

SQL> UPDATE ( SELECT a.empno, b.empno, b.ename emp_ename, a.ename e1,
  2                  b.hiredate h2, a.deptno, b.deptno
  3          FROM   emp a, emp_ram b
  4          WHERE  a.empno = b.empno
  5          AND    a.deptno <> b.deptno )
  6     SET emp_ename = e1,
  7         h2 = TRUNC ( SYSDATE );

2 rows updated.

SQL> select * from emp_ram;

     EMPNO ENAME      HIREDATE      DEPTNO
---------- ---------- --------- ----------
      7876 ADAMS      08-JAN-09         10
      7654 MARTIN     08-JAN-09         10
      7839            17-NOV-81         10

SQL> rollback;

Rollback complete.

The both update statements updated 2 rows.
Then why first update statement give as "3 rows updated."?
I need this in procedure where i am updating table from another table and i want to use sql%rowcount to know how many rows are getting updated.
In above case for both update statement sql%rowcount is different even both update statement is doing equal number of
updates.

Regards,
Ram.

Re: sql%rowcount difference in two update statements. [message #379916 is a reply to message #379914] Thu, 08 January 2009 06:59 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
The both update statements updated 2 rows.
Then why first update statement give as "3 rows updated."?

I would believe Oracle - as you see the first statement updated 3 rows, the second one updated 2 rows.
As there is no WHERE condition in the first UPDATE, it updated all (3) rows in EMP_RAM.
As the subquery in the second UPDATE returns two rows (the row with EMP_NO = 7839 is not joined to any row in EMP), two rows are updated.
Re: sql%rowcount difference in two update statements. [message #380047 is a reply to message #379916] Thu, 08 January 2009 22:25 Go to previous messageGo to next message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

Thank you flyboy.
I also believe Oracle.
The above is copy paste from SQL*PLUS.
But i am still confused about it.
If first statement updated 3 rows but it is not reflected in output.
Can anybody please explain this in some details?

Regards,
Ram.
Re: sql%rowcount difference in two update statements. [message #380056 is a reply to message #380047] Thu, 08 January 2009 23:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
v.ram81 wrote on Thu, 08 January 2009 20:25

If first statement updated 3 rows but it is not reflected in output.
Can anybody please explain this in some details?



It did update the third row, by changing the date to null. Changing to null is an update. If you do not specify a where exists clause in that update syntax, then it updates all rows in the table, setting the values for those that are not in the select clause to null. The following would update just 2 rows:

UPDATE emp_ram a
SET ( ename, hiredate ) =
          ( SELECT ename, TRUNC ( SYSDATE )
           FROM   emp b
           WHERE  a.empno = b.empno
           AND    a.deptno <> b.deptno )
WHERE EXISTS
          ( SELECT ename, TRUNC ( SYSDATE )
           FROM   emp b
           WHERE  a.empno = b.empno
           AND    a.deptno <> b.deptno );

Re: sql%rowcount difference in two update statements. [message #380062 is a reply to message #380056] Fri, 09 January 2009 00:17 Go to previous message
v.ram81
Messages: 50
Registered: April 2006
Location: pune
Member

Thank You Barbara.
Now it is clear.

Regards,
Ram. Thumbs Up
Previous Topic: trouble with non existent constraint
Next Topic: how to identify spaces
Goto Forum:
  


Current Time: Thu Dec 08 10:12:49 CST 2016

Total time taken to generate the page: 0.12415 seconds