DB2 SQL高级应用 High level usage

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.

     EXPORT TO yourfile.del OF DEL SELECT PRICE.* from yourtable

     Import

     import from yourfile.del of del COMMITCOUNT 100000 insert into yourtable; // import with matched columns, // assume that the first column is the PK with increased auto IMPORT FROM yourfile.del OF DEL METHOD P ( 1, 2 ) COMMITCOUNT 100000 INSERT INTO yourschema.table( column2, column3 );

5.  MERGE INTO

     MERGE INTO TARGET_TABLE USING SOURCE_TABLE WHEN MATCHED THEN UPDATE ()=() WHEN NOT MATCHED THEN INSERT () VALUES () 

     例子:

     StringBuffer sb = new StringBuffer() .append( "merge into(select * from wwprt.price_pattern_ref_cn where pricepatternid=?)as oo" ) .append( " using(values(cast(? as varchar(2)),cast(? as integer),cast(? as varchar(3)),cast(? as char(1)),cast(? as decimal(13,2)),cast(? as decimal(13,2)))," ) .append( "(cast(? as varchar(2)),cast(? as integer),cast(? as varchar(3)),cast(? as char(1)),cast(? as decimal(13,2)),cast(? as decimal(13,2)))," ) .append( "(cast(? as varchar(2)),cast(? as integer),cast(? as varchar(3)),cast(? as char(1)),cast(? as decimal(13,2)),cast(? as decimal(13,2)))," ) .append( "(cast(? as varchar(2)),cast(? as integer),cast(? as varchar(3)),cast(? as char(1)),cast(? as decimal(13,2)),cast(? as decimal(13,2)))," ) .append( "(cast(? as varchar(2)),cast(? as integer),cast(? as varchar(3)),cast(? as char(1)),cast(? as decimal(13,2)),cast(? as decimal(13,2))))" ) .append( " as nn(refid,refproductid,refpricetype,fromsamecable,priceusdcalc,price)" ) .append( " on oo.refid=nn.refid" ) .append( " when matched and nn.refproductid is null then delete" ) .append( " when matched then update set" ) .append( " oo.refproductid=nn.refproductid," ) .append( " oo.refpricetype=nn.refpricetype," ) .append( " oo.fromsamecable=nn.fromsamecable," ) .append( " oo.priceusdcalc=nn.priceusdcalc," ) .append( " oo.price=nn.price" ) .append( " when not matched and nn.refproductid is not null then" ) .append( " insert(pricepatternid,refid,refproductid,refpricetype,fromsamecable,priceusdcalc,price)" ) .append( " values(?,nn.refid,nn.refproductid,nn.refpricetype,nn.fromsamecable,nn.priceusdcalc,nn.price)" ); return sb.toString();