Archives of the TeradataForum

Message Posted: Wed, 10 Aug 2005 @ 12:15:41 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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.

_______________________
Dave Clough
Database Designer
Express ICS

	<Prev	Next>		<<First	<Prev	Next>	Last>>