Re: Using sql to delete duplicate records

On Mon, Oct 5, 2015 at 11:07 AM, Alan Shore <ashore@xxxxxxxx> wrote:

This is driving me nuts (I know - not that far to drive)
I used the following sql to delete duplicate records in a file
delete from file01 A where rrn(A) in
(select RRN(B) from file01 B, file01 C where
b.IITEM = c.IITEM and RRN(B) > RRN(C))

Preferably, you shouldn't be thinking in terms of RRN at all if you
are using SQL. As such, my recommendation is to not use SQL and just
write a quick RPG program to do it. (Simply read all the records in
keyed order and get rid of anything that's the same as the previous
record.)

I know deletion of duplicates in SQL must be a common question, so I
just spent some time Googling around. The profusion of different
approaches, even if you narrow yourself to DB2, strongly suggests to
me that lots of people are getting this wrong when they try to do it
in SQL. I'm sure there are some who are getting it right, but in the
time it takes for you to sift through and find the right answer(s),
you could have written and run the RPG program already.

John Y.