Re: Deleting duplicates from table

I have done this, so this is from "distant" memory off the top of my head, so to speak...

What I do remember is using the RRN() for each record to differentiate between duplicates. I think it would be a join of the table to itself with the join based on all the columns you want to make unique again, but where the primary join file having the higher RRN. That can be a WITH clause to feed the "main" clause to delete the one(s) that have the higher RRN.
I picked the higher one IIRC I'm sure based on the fact that usually the lower RRN would be the first one.
I think shops should be more friendly to having a UNIQUE keyword on the physical file, or at least the one with the most important key field list, even if one must invent it. I don't like using the RRN except for things like the purpose of cleaning out duplicates.
-Alan Cassidy

On 11/05/2024 10:33 AM EST Vinay Gavankar <vinaygav@xxxxxxxxx> wrote:


Hi,

I am trying to delete duplicate from a table, using following sql (based on
example from https://www.ibm.com/docs/en/db2/11.5?topic=statements-delete )

delete from
(select rownumber() over (partition by flda, fldb, fldc
order by fldd desc, flde desc)
from myfile) as a(rn)
where rn > 1

I get an error over the first '(' before select:
Token ( was not valid. Valid tokens: <IDENTIFIER>.

What am I doing wrong?

TIA
Vinay
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.