Solved

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

Posted on 2007-04-11
5
3,157 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

623 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