I have a table DOMAINS
in 2 different schemas with columns ID
, NAME
,CODE
,DESCRIPTION
.
For any NAME
exist in new schema, it should use existing ID
without any merge; for those new NAME
records, it should insert with ID
from old schema.
MERGE INTO domains a
USING ( SELECT id, name, code, description
FROM <Old Schema 6.1>.domains@db_mig_61_to_74 ) b
ON ( a.name = b.name )
WHEN MATCHED **<do nothing>**
WHEN NOT MATCHED THEN INSERT( a.id, a.name, a.code, a.description )
VALUES( b.id, b.name, b.code, b.description );
How can i intepret the portion of do nothing
in above query?
For your case, no need to use the part:
WHEN MATCHED THEN UPDATE ...
( using WHEN MATCHED THEN UPDATE SET a.id = a.id
is accepted(Oracle doesn't hurl) but has no impact, so, such a usage is redundant, because you don't want to change anything for the matching case. )
If you wanted to change, then add
WHEN MATCHED THEN UPDATE SET a.id = b.id
before
WHEN NOT MATCHED THEN INSERT...
( e.g.Oracle supports
WHEN MATCHED THEN UPDATE
syntax. Refer the Demo below )
Go on with the following for the current case :
SQL> CREATE TABLE domains(
id INT,
name VARCHAR2(50),
code VARCHAR2(50),
description VARCHAR2(50)
);
SQL> INSERT INTO domains VALUES(1,'Domain A','D.A.','This is Domain A');
SQL> MERGE INTO domains a USING
(SELECT 2 id, 'Domain A' name, 'D.A.' code, 'This is Domain A' description
FROM domains) b
ON ( a.name = b.name )
WHEN NOT MATCHED THEN INSERT( a.id, a.name, a.code, a.description )
VALUES( b.id, b.name, b.code, b.description );
SQL> SELECT * FROM domains;
ID NAME CODE DESCRIPTION
-- -------- ----- ----------------
1 Domain A D.A. This is Domain A
SQL> DELETE domains;
SQL> INSERT INTO domains VALUES(1,'Domain A','D.A.','This is Domain A');
-- we're deleting and inserting the same row again
SQL> MERGE INTO domains a USING
(SELECT 2 id, 'Domain B' name, 'D.B.' code, 'This is Domain B' description
FROM domains) b
ON ( a.name = b.name )
WHEN NOT MATCHED THEN INSERT( a.id, a.name, a.code, a.description )
VALUES( b.id, b.name, b.code, b.description );
SQL> SELECT * FROM domains;
ID NAME CODE DESCRIPTION
-- -------- ----- ----------------
1 Domain A D.A. This is Domain A
2 Domain B D.B. This is Domain B
Btw, the part followed by the USING
keyword no need to be a subquery, but a table or a view. Having evaluated for the current case:
MERGE INTO domains ds --> "ds" : "domains" source
USING v_domains dt --> "dt" : "domains" target
ON ( ds.name = dt.name )
WHEN NOT MATCHED THEN INSERT( ds.id, ds.name, ds.code, ds.description )
VALUES( dt.id, dt.name, dt.code, dt.description )
might identically be used like the above statement after having created v_domains
view through
CREATE OR REPLACE VIEW v_domains AS
SELECT 2 id, 'Domain A' name, 'D.A.' code, 'This is Domain A' description
FROM domains