update statment 2

[jimmybajwa]

Can we get the no of rows updated in a update statement.
For E.G :
Update
SET emp_project = 'xyz'
Where
emp_join_date between '01/01/2003' and '01/02/2003' ;

How can we get the no of rows updated by this query ?


Thanks
Jimmy Bajwa

 

[billytcj]

If it is using Java API, then u can do as following lines:
...
...

int NoUpdatedRow = statement.executeUpdate("update blah blah..."
System.out.println(NoUpdatedRow);

...
...

Note:
Where u can get the number of updated row in above system print line.

 

[sathyarams]

If you are using command line,

db2 -a "update ..."

-a will display the SQLCA infomration ... sqlerrd(3) will give the number of rows updated/deleted

If using Command Centre , select 'Display SQLCA Information' in Tools-->Options

HTH

Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[gregsimpson]

In a similar manner to that which Sathyaram mentions, if your using COBOL etc you will also be able to get the number of rows affected by querying SLQERRD(3) from the SQLCA.

Cheers
Greg

 

[sathyarams]

Can anyone suggest how to get the number of rows affected if the statement(s) is within a SQL Stored Procedure ... I would want to use the number in the next step in the procedure ...

Thanks

Sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[gregsimpson]

Jimmy,

as deletes have been mentioned in the posts it's probably worth you being aware of

SQLERRD.3 - the number of rows affected after INSERT, UPDATE, and DELETE (but not rows deleted as a result of CASCADE delete). Set to 0 if the SQL statement fails, indicating that all changes made in executing the statement were cancelled. Set to -1 for a mass delete from a table in a segmented table space. For SQLCODE -911 or -913, SQLERRD.3 can also contain the reason code for a timeout or deadlock.

Cheers
Greg

 

[gregsimpson]

Jimmy,Sathyarams,

version 8.1 also has this I found out this morning.

DB2 UDB/V8.1 Cookbook ©
Compound SQL 58

GET DIAGNOSTICS Statement

Syntax

GET DIAGNOSTICS SQL-var-name ROW_COUNT =
RETURN_COUNT

Example
BEGIN ATOMIC
DECLARE numrows INT DEFAULT 0;
UPDATE staff
SET salary = 12345
WHERE ID < 100;
GET DIAGNOSTICS numrows = ROW_COUNT;
UPDATE staff
SET salary = numrows
WHERE ID = 10;
END

Notes
• Returns information about most recently run SQL statement
• In above example, used to get number of rows updated in UPDATE

Cheers
Greg