469,290 Members | 1,882 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,290 developers. It's quick & easy.

MERGE and GET DIAGNOSTICS ROW_COUNT

row_count - is it total inserted,updated or deleted by merge statement?
Any way get different counts?

Andy

Nov 12 '05 #1
1 2603
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.

Similar topics

reply views Thread by Slava Gorski | last post: by
1 post views Thread by - | last post: by
2 posts views Thread by N.V.Dev | last post: by
3 posts views Thread by Rhino | last post: by
8 posts views Thread by Squirrel | last post: by
1 post views Thread by sriramyait | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.