|
|
Archives of the TeradataForum
Message Posted: Mon, 03 Apr 2006 @ 17:35:37 GMT
Subj: | | Re: SAME TABLE UPDATE |
|
From: | | Anomy Anom |
<-- Anonymously Posted: Monday, April 03, 2006 12:04 -->
Hello!
Can use :
UPDATE PRODDSS_DEV.POITOUSE
SET ORD_QTY = DLV_QTY;
An example using UPDATE FROM:
CREATE SET TABLE PRODDSS_DEV.Ax, NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
PO_NBR INTEGER,
ITM_NBR INTEGER,
ENTR_DT INTEGER,
ORD_QTY INTEGER,
DLV_QTY INTEGER)
UNIQUE PRIMARY INDEX XPKAx ( PO_NBR, ITM_NBR, ENTR_DT );
INSERT INTO PRODDSS_DEV.Ax VALUES( 1,1,1,10,100);
INSERT INTO PRODDSS_DEV.Ax VALUES( 1,1,2,11,110);
INSERT INTO PRODDSS_DEV.Ax VALUES( 1,1,3,12,120);
INSERT INTO PRODDSS_DEV.Ax VALUES( 1,2,1,13,130);
INSERT INTO PRODDSS_DEV.Ax VALUES( 1,2,2,14,140);
INSERT INTO PRODDSS_DEV.Ax VALUES( 1,2,3,15,150);
INSERT INTO PRODDSS_DEV.Ax VALUES( 2,1,1,16,160);
INSERT INTO PRODDSS_DEV.Ax VALUES( 2,1,2,17,170);
INSERT INTO PRODDSS_DEV.Ax VALUES( 2,1,3,18,180);
INSERT INTO PRODDSS_DEV.Ax VALUES( 2,2,1,19,190);
INSERT INTO PRODDSS_DEV.Ax VALUES( 2,2,2,20,200);
INSERT INTO PRODDSS_DEV.Ax VALUES( 2,2,3,41,410);
----
CREATE SET TABLE PRODDSS_DEV.Ay, NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
PO_NBR INTEGER,
ITM_NBR INTEGER,
ENTR_DT INTEGER,
ORD_QTY INTEGER,
DLV_QTY INTEGER)
UNIQUE PRIMARY INDEX XPKAy ( PO_NBR, ITM_NBR, ENTR_DT );
INSERT INTO PRODDSS_DEV.Ay VALUES( 1,1,1,310,3100);
INSERT INTO PRODDSS_DEV.Ay VALUES( 1,1,2,311,3110);
INSERT INTO PRODDSS_DEV.Ay VALUES( 1,1,3,312,3120);
INSERT INTO PRODDSS_DEV.Ay VALUES( 1,2,1,313,3130);
INSERT INTO PRODDSS_DEV.Ay VALUES( 1,2,2,314,3140);
INSERT INTO PRODDSS_DEV.Ay VALUES( 1,2,3,315,3150);
INSERT INTO PRODDSS_DEV.Ay VALUES( 2,1,1,316,3160);
INSERT INTO PRODDSS_DEV.Ay VALUES( 2,1,2,317,3170);
INSERT INTO PRODDSS_DEV.Ay VALUES( 2,1,3,318,3180);
INSERT INTO PRODDSS_DEV.Ay VALUES( 2,2,1,319,3190);
INSERT INTO PRODDSS_DEV.Ay VALUES( 2,2,2,320,3200);
INSERT INTO PRODDSS_DEV.Ay VALUES( 2,2,3,341,3410);
---
UPDATE PRODDSS_DEV.Ax
FROM PRODDSS_DEV.Ay b
SET ORD_QTY = b.DLV_QTY
WHERE PRODDSS_DEV.Ax.PO_NBR = b.PO_NBR
AND PRODDSS_DEV.Ax.ITM_NBR = b.ITM_NBR
AND PRODDSS_DEV.Ax.ENTR_DT = b.ENTR_DT;
Bye.
| |