MERGE statement(SQL)

merge_stmt.sql

-- Truncate tables
DROP table staging_members;
DROP table members;

-- Create members
CREATE TABLE members(
    id number primary key,
    first_name varchar(50) not null,
    last_name varchar(50),
    rank varchar(20)
);

-- members: default records
INSERT INTO members (id, first_name, last_name, rank) 
  WITH names AS ( 
    SELECT 1, 'Santhosh',     'Kumar',      'Silver'    FROM dual UNION ALL 
    SELECT 2, 'Shaktish', 'Kumar', 'Gold'   FROM dual UNION ALL 
    SELECT 3, 'Seenu',   '',     'Platinum' FROM dual UNION ALL 
    SELECT 4, 'Thulasi',     'Dharan',      'Platium'     FROM dual 
  ) 
  SELECT * FROM names;
  
-- Create staging_members from members schema
create table staging_members as select * from members;

-- View table records
select * from members order by ID;
select * from staging_members order by ID;
  
-- Update members
INSERT INTO members (id, first_name, last_name, rank) 
WITH names AS ( 
    SELECT 5, 'Abhinaya',     'Sivan',      'Gold'    FROM dual UNION ALL 
    SELECT 6, 'Krishna', 'Sri', 'Silver'   FROM dual
) SELECT * FROM names;
update members set last_name = 'Babu' where id = 1;
update members set first_name = 'Sreenivasa', last_name ='Kumar' where id = 3;

-- Update staging_members(invalid records)
INSERT INTO staging_members (id, first_name, last_name, rank) 
WITH names AS ( 
    SELECT 90, 'Hide',     'Seek',      'Made with choco'    FROM dual UNION ALL 
    SELECT 124, 'Bourbon', '', 'Made with cream'   FROM dual
) SELECT * FROM names;



-- Merge into staging_members(target) using members(source)
MERGE INTO  staging_members 
USING       (select ID, first_name, last_name, rank from members) members
ON          (staging_members.ID = members.ID)
WHEN MATCHED THEN
    UPDATE SET
            staging_members.first_name = members.first_name,
            staging_members.last_name = members.last_name,
            staging_members.rank = members.rank
            WHERE 
                staging_members.first_name <> members.first_name 
                or staging_members.rank <> members.rank
                or staging_members.last_name <> members.last_name
    DELETE WHERE staging_members.id not in (select id from members)
WHEN NOT MATCHED THEN
    INSERT (staging_members.ID, staging_members.first_name, staging_members.last_name, staging_members.rank)
        VALUES (members.ID, members.first_name, members.last_name, members.rank);
-- View table records
select * from members order by ID;
select * from staging_members order by ID;

delete from staging_members where id not in (select id from members);
select * from staging_members order by ID;