DB2 SQL高级应用 High level usage
来源:互联网 发布:corel有多少软件 编辑:程序博客网 时间:2024/05/22 03:36
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
例子:
- DB2 SQL高级应用 High level usage
- High Level Concurrency Objects 高级并发对象
- 高级语言(High-level programming language)
- DB2 USAGE
- 高级并发对象(High Level Concurrency Objects)
- SQL server Profiler and excution plan usage for CPU usage high analysis
- High-level Synthesis Vendors
- coach high level member
- High Level Networking Concepts
- high level tech
- 11.High Level Consumer
- Kafaka High Level Consumer
- 常用SQL:高级应用
- SQL高级应用
- SQL server高级应用
- 常用SQL:高级应用
- SQL索引高级应用
- SQL高级应用
- 用异常来处理错误----第一节 Java异常的概念
- Solaris 环境中的 USB
- CreateThread()线程创建函数,AfxBeginThread等
- python+twistd+nevow安装NOTE
- QQ2009 Beta 1发布 会员优先体验
- DB2 SQL高级应用 High level usage
- 程序的内存分配
- 为了活着~为了爱情~为了年轻过
- 疯狂JAVA讲义---第十二章:Swing编程(二)特殊容器
- 制造业中大规模生产方法带来的好处
- 在MFC类中各种类的指针的获取和应用
- 1730 Perfect Pth Powers 抄题报告
- jpa如何使用hibernate的evict()方法
- 模板成员函数为什么不能是虚函数