 |
 |
Archives of the TeradataForum
Message Posted: Fri, 20 Aug 2004 @ 11:10:01 GMT
| Subj: | | Re: DELETE recommendations? |
| |
| From: | | Dieter Noeth |
Mirjam Berger wrote:
| | solution. It does not delete rows where the PK is the same, but the later attributes differ. | |
Of course not ;-)
It does exactly what the OP wanted: delete one row with the same pk1/pk2 combination but different pk3.
| | A subquery with qualified Rank could do the job to identify exactly one row to be kept - but the makers at NCR did not see fit to
allow subqueries the use of Rank ;) | |
There's a workaround for that kind of syntax restriction, put in in a Derived Table :-)
Matthew's example rewritten:
DELETE FROM TAB
WHERE (PK1, PK2, PK3) IN
(
SELECT * FROM
(
SELECT PK1, PK2, PK3
FROM TAB
WHERE (PK1, PK2) IN
(SELECT
PK1, PK2
FROM TAB
GROUP BY 1,2
HAVING COUNT(*) > 1)
QUALIFY CSUM(1, PK1, PK2) > 1
) dt
)
Dieter
| |
