How to use merge statement

Here is an example:

Table:

Create table t1(id number, hours date, total number);

merge into t1 a
using (select 200 id, to_date(‘2004-12-28 10′,’yyyy-mm-dd hh24’) hours from dual ) b
on(a.id=b.id and a.hours=b.hours)
when matched then
update set total=a.total+1
when not matched then
insert (a.id,a.hours,a.total) values(b.id,b.hours,1);

Note:

a. Statement after merge into and using should be table.

b. Statement after matched should be update statement; Statement after not matchedshould be insert statement.

c. Should not update columns in on statement.

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to How to use merge statement

  1. neworacledba says:

    insert + update — merge…clearly explained

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: