Archives of the TeradataForum
Message Posted: Wed, 10 Aug 2005 @ 12:15:41 GMT
I've got an error message
"5750: The select subquery from the table subquery must fully specify either a UPI value or a USI value."
back from the following test query :
MERGE INTO DEVG123AHE.BOOKINGORDER_TEST1 USING (SELECT ORD_SRKY_ID ,COM_ID ,BUL_ID ,ORD_ID ,ORD_SRCE_SYSTEM_CD ,ORD_SEQ_NR ,ORD_CONT_VL ,ORD_CONT_WT FROM DEVG123AHE.BOOKINGORDER1) AS INSANDUPD (ORD_SRKY_ID, COM_ID,BUL_ID ,ORD_ID ,ORD_SRCE_SYSTEM_CD ,ORD_SEQ_NR ,ORD_CONT_VL ,ORD_CONT_WT) ON INSANDUPD.ORD_SRKY_ID=ORD_SRKY_ID WHEN MATCHED THEN UPDATE SET ORD_CONT_VL=INSANDUPD.ORD_CONT_VL + 1 WHEN NOT MATCHED THEN INSERT VALUES ( INSANDUPD.COM_ID ,INSANDUPD.BUL_ID ,INSANDUPD.ORD_ID ,INSANDUPD.ORD_SRKY_ID ,INSANDUPD.ORD_SRCE_SYSTEM_CD ,INSANDUPD.ORD_SEQ_NR ,INSANDUPD.ORD_CONT_VL ,INSANDUPD.ORD_CONT_WT );
which would be fair enough, but as far as I can tell, they've both got UPIs on:
IndexName ColumnName Type Unique IndexNumber ColumnPosition ORD_SRKY_ID Primary Y 1 1
for both tables.
My question is twofold :
Can anyone see any obvious error that I've made in the SQL (DML, whatever)
Does the restriction of number of rows back from the subquery mean only 1 row as a result set, or 1 row per joining column.
The second question (which I wouldn't have needed to ask if I could test it without that error message) will tell me whether I can use MERGE INTO to replace our existing FastLoad process, in which we delete and re-insert rows. This process effectively re-adds the updated rows and inserts new rows.
Thanks in advance of superior knowledge.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|