How to decompose the sql%rowcount of MERGE statement

Question

Is it possible to get number of rows inserted and updated separately in merge query?

SQL%COUNT gives total number of rows merged.

You could write a package. see the answer. – Lalit Kumar B Mar 02 '15 at 10:21 — Lalit Kumar B, Mar 02 '15 at 10:21

Lalit Kumar B · Answer 1 · 2015-03-02T13:43:13.663

0

SQL%COUNT gives total number of rows merged.

sql℅rowcount gives you the row count of the total rows affected by the MERGE statement. It cannot decompose the INSERT/UPDATE/DELETE row count.

But you could decompose sql%rowcount for Merge by writing

edited Mar 02 '15 at 13:43

answered Mar 02 '15 at 10:01

Lalit Kumar B

47,486
13
97
124

score 0 · Answer 2 · answered Nov 29 '21 at 10:04

With knowledge of total number of rows before and after merge, you can utilize this information in simple arithmetic computation:

                ┌───────────────┐
            ▲ ▲ │               │
            │ │ │               │
            │ │ │               │
            │ │ │               │
count_before│ │ │               │
            │ │ │               │
            │ │ │               │
            │ │ │UUUUUUUUUUUUUUU│ ▲                ▲
            │ │ │UUUUUUUUUUUUUUU│ │rows_updated    │
            ▼ │ │UUUUUUUUUUUUUUU│ ▼                │
              │ │IIIIIIIIIIIIIII│ ▲                │sql%rowcount
   count_after│ │IIIIIIIIIIIIIII│ │rows_inserted   │
              │ │IIIIIIIIIIIIIII│ │                │
              ▼ │IIIIIIIIIIIIIII│ ▼                ▼
                └───────────────┘

select count(*) into count_before from table_name;
merge into table_name ...;
rows_merged := sql%rowcount;
select count(*) into count_after from table_name;
rows_inserted := count_after - count_before;
rows_updated := rows_merged - rows_inserted;

(credit to: asciiflow.com)

you cannot be sure that your merge statement is the only statement that runs on that table at that time, so [count(*) after - count(*) before] makes no sense, or I'm missing something? — Oguen, Aug 09 '22 at 12:07
@Oguen your point is valid, the solution in my answer is safe only if the script is the only script being run on the db. OP didn't describe his usecase so I had mine on my mind where ETL makes initial import and there is no threat of race condition. To be fair, I guess that if the hypothetical `sql%insertedcount` and `sql%updatedcount` attributes existed they would hardly be useful in heavily concurrent environment anyway. — Tomáš Záluský, Aug 14 '22 at 14:35

score -1 · Answer 3 · answered Mar 02 '15 at 14:09

-1

A solution could be that whenever you insert or update one row.. you insert a code in your table, for example:

code=1 --> if insert
code=2 --> if update

so use that condition in where clause when you need count

answered Mar 02 '15 at 14:09

rockerchain

31
8

David Fowler · Answer 4 · 2022-01-24T14:45:03.443

-1

add a delete_count = count_before - count_after. would change the result for insert update counts if count_after is less than count_before.

case for counting deletes with merge condition seperate statement to know how many were deleted. sql%rowcount could do that.

edited Jan 24 '22 at 14:45

answered Jan 21 '22 at 16:04

David Fowler

1
1

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 21 '22 at 16:05

How to decompose the sql%rowcount of MERGE statement

4 Answers4