Assumes that we have a table named PRICE_CN
Columns: ID, CABLEID, PRODUCTID, PRICE
Records: 50000000, ANND1, 609, 90
1. NEW TABLE, OLD TABLE
-- a .With Update
SELECT * FROM OLD TABLE (
UPDATE PRICE_CN PRICE SET PRICE = 80 WHERE PRICE.ID = 50000000
)
--Results: The price before updated will be selected out
-- PRICE
-- 90
-- b.
-- Assumes that we only have 1 record before insert successful
SELECT COUNT(*) FROM OLD TABLE(
INSERT INTO PRICE_CN ( CABLEID, PRODUCTID, PRICE ) VALUES ( 'ANND2', 700, 1000)
)
-- Results:
-- Count
-- 1
2. INSERT WITH SELECT
-- set the price of the cable-product with the cable id 'ANND2' and productid 700 to be the price of the new cable product with cable id 'ANND3' and productid 701
INSERT INTO PRICE CN ( CABLEID, PRODUCTID, PRICE )
SELECT 'ANND3', 701, PRICE.PRICE FROM PRICE_CN AS PRICE WHERE PRICE.CABLEID='ANND2', PRICE.PRODUCTID=700
-- the results of the sub select will be the values to be insert into PRICE_CN table
3. Temp table using WITH
// Test How to select out the OLD Price with the NEW price
WITH
BEFORE_PRICE_UPDATED
AS(
SELECT * FROM OLD TABLE (
UPDATE WWPRT.PRICE_CN PRICE SET PRICE = 80 WHERE PRICE.ID = 50000000
)
)
SELECT PRICE.CABLEID, PRICE.PRODUCTID, PRICE.PRICE AS OLDPRICE, BEFOREPRICE.PRICE AS NEWPRICE
FROM WWPRT.PRICE_CN PRICE
INNER JOIN BEFORE_PRICE_UPDATED BEFOREPRICE ON BEFOREPRICE.ID = PRICE.ID AND PRICE.ID = 50000000
-- results: AFTER UPDATE, we can compare with the new one selected out from the OLD table as a tempoary table
CABLEID PRODUCTID OLDPRICE NEWPRICE
---------- ------------ ----------- -----------
ANND1 609 90 80
-- The format using WITH
WITH
TEMP1(T1COL1,T1COL2,....) AS (
SELECT COL1, COL2 FROM TABLE1 WHERE ....
),
TEMP2(T2COL,T2COL2.....) AS(
values (T2COLValue1, T2COL2Value1 ....),
(T2COLValue2, T2COL2Value2 ....),
(T2COLValue3, T2COL2Value3 ....)
) // no comma here
SELECT * FROM TABLE1, TABLE2, TEMP1, TEMP2 ......
4. Import and Export
Export.
Import
5. MERGE INTO
例子: