Basic usage of merge into

Because merge into is rarely used normally, but this time it is used to insert and update records, so simply write down the most basic usage. The example here is to update the value count of the eligible data in a table. If you find a record that meets the ID condition, add 1 to its value field, otherwise, insert the new record and initialize the value.

Create a test table and insert data:

Create table test1 (id number, val number)

Insert into test1 values (101,1)

Insert into test1 values (102,1)

Commit

Select * from test1

ID VAL

--

101 1

102 1

Do the merge into operation and a new piece of data is inserted:

Merge into test1 t1

Using (select count (*) cnt from test1 where id = 103) T2 on (cnt 0)

When matched then

Update set val = val + 1 where id = 103

When not matched then

Insert values (103,1)

Commit

Select * from test1

ID VAL

--

101 1

102 1

103 1

After performing another merge into, the data is updated:

ID VAL

--

101 1

102 1

103 2