Solved

PL/SQL MERGE STATEMENT.  I need a count of both inserted and updated rows.

Posted on 2007-04-11
5
3,136 Views
Last Modified: 2009-03-10
I have the following merge statement that either inserts a new record OR updates a record.  Then it prints out the number of rows merged.

I want to be able to print out BOTH number of rows inserted and number of rows updated.  Is there a way to do this, and how would it be done?

MERGE INTO dm_table dm
      USING (SELECT *
               FROM mytable              WHERE status_ = c_load_stat_new) dmld
      ON (dm.id= dmld.dm_physician_id)
      WHEN MATCHED THEN
         UPDATE
            SET a =a1
      WHEN NOT MATCHED THEN
         INSERT
            (a)
         VALUES
            (a1);
      dbms_output.put_line(to_char(SQL%ROWCOUNT) || ' rows merged.');
      --
            COMMIT;
0
Comment
Question by:mintsii
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 250 total points
ID: 18892202
Do before and after row counts so you can compute the difference.
0
 

Author Comment

by:mintsii
ID: 18892247
That was the one option I had considered, however, I was wondering if there was another way to handle it.  Perhaps there is a function like sql%rowcount that counts inserts or updates?
0
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 250 total points
ID: 18895454
I am not aware of a direct psuedo column that gives the insert and update breakup.
But, there are a couple of work arounds that you can use to get these counts.

The easiest one would be:
 - use the same join condition to get the counts as shown in this example.

declare
 l_upd_c number := 0;
 l_ins_c number := 0;

begin

 select count(*) into l_upd_c
 from emp2, (select * from emp where sal is not null) x
 where (emp2.ename = x.ename);

 select count(*) into l_ins_c
 from (select * from emp where sal is not null) x;

 l_ins_c := l_ins_c - l_upd_c;

 merge into emp2
 using (select * from emp where sal is not null) x
 on (emp2.ename = x.ename)
 when matched then
  update
  set sal = sal
 when not matched then
  insert
  values(x.ename, x.sal );

if sql%rowcount = l_upd_c + l_ins_c then
 dbms_output.put_line(l_upd_c);
 dbms_output.put_line(l_ins_c);
end if;

end;
/

A better(but more complex) way is to use a separate package to get the counts. See the following link:
http://www.revealnet.com/newsletter-v4/0903_D.htm
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dump data from mysql to xls php 10 52
Move a Database to a different server 4 57
Dbms_job.change procedure 16 29
TDE for SQL Web Edition 1 32
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question