merge.sql

application/octet-stream

Download

--
-- testing the MERGE INTO ... command
--

-- Create the test dataset

CREATE TABLE Stock (
    item_id   INT UNIQUE,
    balance   INT
);

INSERT INTO Stock VALUES (10, 1000);

CREATE TABLE Buy (
    item_id 	INT,
    amount	  INT
);

INSERT INTO Buy VALUES (10, 500);
INSERT INTO Buy VALUES (20, 2000);

CREATE TABLE Sale (
    item_id 	INT,
    amount	  INT
);

INSERT INTO Sale VALUES (10, 1500);
INSERT INTO Sale VALUES (20, 700);


--Test the UPDATE + INSERT operation on stock with MERGE 
MERGE INTO Stock USING Buy 
			ON Stock.item_id = Buy.item_id
 	WHEN MATCHED THEN UPDATE SET balance = balance + Buy.amount
 	WHEN NOT MATCHED THEN INSERT VALUES (Buy.item_id, Buy.amount);

--Look the result
SELECT * FROM Stock ORDER BY item_id;


-- Test the UPDATE+DELETE operation on stock with action quals
MERGE INTO Stock USING Sale 
			ON Stock.item_id = Sale.item_id
   WHEN MATCHED AND balance - amount > 0 THEN UPDATE SET balance = balance - amount
   WHEN MATCHED THEN DELETE;
   
   
SELECT * FROM STOCK ORDER BY item_id;
 
--clean up
DROP TABLE Stock, Buy, Sale;