Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: MERGE INTO
 
From:   David Clough

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016