Merge

You cannot update the same row more than once in a Merge statement.
Fine-grained access control is not implemented during MERGE. If needed, use Insert and Update.

Clear Screen
------------------------------------------------------
-- Create Dept
------------------------------------------------------
Drop Table Dept Cascade Constraints;
Create Table Dept (
 Deptno              Number(2) Not Null,
 Dname               Varchar2(14),
 Loc                 Varchar2(13),
     Constraint Dept_Deptno_Pk Primary Key (Deptno) );

Insert Into Dept Values (10,'ACCOUNTING','NEW YORK');
Insert Into Dept Values (20,'RESEARCH','DALLAS');
Insert Into Dept Values (30,'SALES','CHICAGO');
Insert Into Dept Values (40,'OPERATIONS','BOSTON');

------------------------------------------------------
-- Create Dept2 - holds records to be inserted/updated
------------------------------------------------------
Drop Table Dept2 Cascade Constraints;

Create Table Dept2 As Select * From Dept Where 1=2;

Insert Into Dept2 Values (10,'ACCOUNTING'  ,'PITTSBURGH');
Insert Into Dept2 Values (40,'OPERATIONS'  ,'BOSTON');
Insert Into Dept2 Values (50,'HOUSEKEEPING','WASHINGTON DC');
Commit;

------------------------------------------------------
-- Display the table data before the Merge
------------------------------------------------------
Prompt Table: Dept -- Before Merge
Select * From Dept Order By Deptno;

Prompt
Prompt Table: Dept2
Select * From Dept2 Order By Deptno;

------------------------------------------------------
-- Merge Dept2 into Dept
------------------------------------------------------
Merge Into Dept D1
Using (Select Deptno, Dname, Loc
         From Dept2) D2
   On (D1.Deptno = D2.Deptno)
 When     Matched Then Update Set D1.Loc = D2.Loc
                       Delete Where (Loc = 'BOSTON')
 When Not Matched Then Insert (D1.Deptno, D1.Dname, D1.Loc)
                       Values (D2.Deptno, D2.Dname, D2.Loc);
Commit;

------------------------------------------------------
-- Show dept table data after merge
------------------------------------------------------
Prompt
Prompt Table: Dept -- After Merge
Select * From Dept Order By Deptno;