Merge into and mergeinto

Merge into and mergeinto

Today, we have A strange demand: system A is the main system, and the terminal PDA uploads data to system. If there is A document id that can be found in system A's id, update Status; if the Document id is not found in system A, insert. The following is a simple test.

Drop table test;
Create table test (id number primary key, name varchar2 (10), state number (1 ));
Insert into test values (1, 'document 1', 1 );
Insert into test values (2, 'document 2', 1 );
Insert into test values (3, 'document 3', 1 );
Commit;

1. When I saw this requirement, I thought of using merge into, so I wrote the following SQL:
Merge into test t1
Using (select id from test where name = ?) T2
On (t1.id = t2.id)
When matched then
Update set state =?
When not matched then
Insert (id, name, state) values (?, ?, ?);
When the select id from test where name =? Values can be updated successfully. If there is no value, insert is not performed.

2. Improvement
Merge into test t1
Using (select id from test where name = 'document 1'
Union select-1 from dual) t2
On (t1.id = t2.id)
When matched then
Update set state = 2
When not matched then
Insert (id, name, state) values (1, 'document 1', 1 );
There is also a problem with these writes. When select id from test where name = ?, Select id from test where name = 'document 1' when data cannot be found
Yes. primary key conflict will be reported when data can be found.
Merge into test t1
*
Row 3 has an error:
ORA-00001: violation of unique constraints (SYS_C0065462)

3. Make sure that a record is found in using, no matter whether there is a value or not.
Select * from test;
ID NAME STATE
------------------------------
1 Document 1 1 1
2 Documents 2 1
3 documents 3 1
-- The PDA document is available in system
Merge into test t1
Using (select id from test where name = 'document 1'
Union all
Select 1 id
From dual
Where not exists (select id from test where name = 'document 1') t2
On (t1.id = t2.id)
When matched then
Update set state = 2
When not matched then
Insert (id, name, state) values (t2.id, 'document 1', 1 );
-- The document of PDA does not exist in system
Merge into test t1
Using (select id from test where name = 'document 10'
Union all
Select 10 id
From dual
Where not exists (select id from test where name = 'document 10') t2
On (t1.id = t2.id)
When matched then
Update set state = 2
When not matched then
Insert (id, name, state) values (t2.id, 'document 1', 1 );

Select * from test;
ID NAME STATE
-----------------------------
1 Document 1 2
2 Documents 2 1
3 documents 3 1
10 documents 1 1 1