Archives of the TeradataForum

Message Posted: Sun, 03 Jun 2007 @ 22:54:17 GMT

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

Based upon an update to Target from Source, I know I will get a 'multiple source row' problem - it's in the nature of my incoming data.

What I'd like to be able to do is to Update the Target table from Source (using an Update through a Join) but Qualifying on the latest or earliest duplicate row (for a given key), as I see fit.

For, my humorous example (perhaps not!) :

     CT TEA_MAKING (MAKER_NM CHAR(8), MADE_QT FLOAT, INCLUDE_COFFEE_IN CHAR(1))
                 UNIQUE PRIMARY INDEX (MAKER_NM);  /* This is the Target */

     CT TEA_MADE (MAKER_NM CHAR(8), MADE_QT BYTEINT, INCLUDE_COFFEE_IN CHAR(1))
                 PRIMARY INDEX (MAKER_NM); /* This is the Source */

     INS INTO TEA_MADE VALUES ('CLOUGH', 1, 'Y')

     INS INTO TEA_MAKING SELECT * FROM TEA_MADE;

     DELETE FROM TEA_MADE;

     INS INTO TEA_MADE VALUES ('CLOUGH', 2, 'Y');
     INS INTO TEA_MADE VALUES ('CLOUGH', 1, 'N');

     UPDATE TEA_MAKING TGT1
     SET
     MADE_QT =+ TEA_MADE.MADE_QT,
     INCLUDE_COFFEE_IN=TEA_MADE.INCLUDE_COFFEE_IN
     WHERE
     TGT1.MAKER_NM=TEA_MADE.MAKER_NM
     ;

     7547: Target row updated by multiple source rows (as expected)

What I'd like to do is to write this directly :

     UPDATE TEA_MAKING TGT1
     SET
     MADE_QT =+ TEA_MADE.MADE_QT,
     INCLUDE_COFFEE_IN=TEA_MADE.INCLUDE_COFFEE_IN
     WHERE
     TGT1.MAKER_NM=TEA_MADE.MAKER_NM
     QUALIFY ROW_NUMBER() OVER (PARTITION BY TEA_MADE.MAKER_NM ORDER BY
     TEA_MADE.INCLUDE_COFFEE_IN, TEA_MADE.MADE_QT DESC) = 1;

but the syntax won't allow it (or at least not the way I've written it).

I can, of course, create another (de-duplicated) work table, using an INS-SEL with the Qualify statement, and then apply the Update statement basing upon the de-duplicated table (easy enough). I'd just like to do it in one statement.

Anyone done this ?

Dave Clough
Database Designer
Express ICS

www.tnt.com

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