On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Wednesday, August 27, 2008

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;

No comments:

Post a Comment