By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE domains(
id INT,
name VARCHAR2(50),
code VARCHAR2(50),
description VARCHAR2(50)
)
INSERT INTO domains VALUES(1,'Domain A','D.A.','This is Domain A')
1 rows affected
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 )
SELECT * FROM domains
| ID | NAME | CODE | DESCRIPTION |
|---|---|---|---|
| 1 | Domain A | D.A. | This is Domain A |
DELETE domains
1 rows affected
INSERT INTO domains VALUES(1,'Domain A','D.A.','This is Domain A')
1 rows affected
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 )
1 rows affected
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 |
DELETE domains
2 rows affected
INSERT INTO domains values(1,'Domain A','D.A.','This is Domain A')
1 rows affected
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 MATCHED THEN UPDATE SET a.id = b.id -- If you wanted to change for the matching case
WHEN NOT MATCHED THEN INSERT( a.id, a.name, a.code, a.description )
VALUES( b.id, b.name, b.code, b.description )
1 rows affected
SELECT * FROM domains
| ID | NAME | CODE | DESCRIPTION |
|---|---|---|---|
| 2 | Domain A | D.A. | This is Domain A |