Archives of the TeradataForum
Message Posted: Sun, 03 Jun 2007 @ 22:54:17 GMT
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 ?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|