By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,779 Members | 1,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,779 IT Pros & Developers. It's quick & easy.

MERGE and GET DIAGNOSTICS ROW_COUNT

P: n/a
row_count - is it total inserted,updated or deleted by merge statement?
Any way get different counts?

Andy

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
bughunter@ru wrote:
row_count - is it total inserted,updated or deleted by merge statement?
Any way get different counts?

Andy

Good one... we pondered this question quite a bit.
http://publib.boulder.ibm.com/infoce...6c%63%61%22%20
"After INSERT, UPDATE, DELETE, or MERGE, contains the actual number of
rows that qualified for the operation."
The rows "qualifying for MERGE" are all the rows which don't hit the
"ELSE IGNORE" section. That is, it is the sum of all rows processed by
each WHEN [NOT] MATCHED branch.

The owrds were chosen thinking about INSTEAD OF trigger where rowcount
could be any value, yet the insert trigger just dumped the rows to
nirwana or did all sorts of complex stuff.

Eventually I'd like to see this:
SELECT COUNT(*) cnt, mode FROM
NEW TABLE(MERGE INTO T INCLUDE (mode VARCHAR(10)
USING S ON T.pk = S.pk
WHEN MATCHED THEN UPDATE SET c1 = S.c1,
mode = 'UPDATE'
WHEN NOT MATCHED THEN INSERT VALUES (pk, c1, 'INSERT'))
GROUP BY mode;

*Thinking of my happy-place with infinite resources*
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.