Number of ROWS updated using UPDATE statement

[bartha]

Hi,
I need to capture the number of rows that were updated using UPDATE command.

For Example:

UPDATE t1
SET Col_1 = 'Col_1', Col_2 = 'Col_2'
WHERE Col_3 = 'TEK'
AND Col_4 = 'TIP'

Once this is executed, I need to be able to capture the number of rows that was updated. What might be the best way to include COUNT( ) function within the UPDATE syntax as used.

bartha

 

[SantaMufasa]

Bartha,

Why not execute:

SELECT COUNT(*) Rows_Updated
WHERE  Col_3 = 'TEK'
AND    Col_4 = 'TIP'
/

...just prior to executing the UPDATE? (How will you be using the "COUNT(*)" information? If you are simply curious about the count, then you can "SET FEEDBACK ON" if you are using SQL*Plus, and SQL*Plus responds with "nnn rows updated".)


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[bartha]

I would like to avoid another SELECT statement to capture the count and find an easy way to track the updates.

If this is the only option, then I may have not much of a choice but use it. This is upposed to be production code and not just a sample SQL-PLUS code.

bartha

 

[SantaMufasa]

Bartha,

Tell us what you plan to do to track the UPDATE count. Are you going to INSERT the count into a tracking table? (We need to have more context to help solve your requests.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[karluk]

If you have the option of programming this in pl/sql, it's easy to do. SQL%ROWCOUNT contains the number of rows affected by the most recently executed sql statement. So all you have to do is note the value of SQL%ROWCOUNT immediately following your update.

I suppose you already know that sql*plus tells you the number of rows updated after executing the update. I don't know how to capture this information for use in a script, however. So if you need to do this in sql rather than pl/sql, I don't know of any good method short of SantaMufasa's suggestion to select a count of the rows that match your update criteria immediately before doing the update.

Here is an illustration of the pl/sql approach:

SQL> select * from t1;

COL_1      COL_2      COL_3      COL_4
---------- ---------- ---------- ----------
a          b          c          d
e          f          TEK        g
h          i          TEK        TIP
j          k          TEK        TIP

SQL> set serveroutput on
SQL> begin
UPDATE t1
SET    Col_1 = 'Col_1',  Col_2 = 'Col_2'
WHERE  Col_3 = 'TEK'
AND    Col_4 = 'TIP';
dbms_output.put_line('Rows updated = '|| sql%rowcount);
end;
/
Rows updated = 2

PL/SQL procedure successfully completed.