How to find duplicate records in table?

Posted by vignesh kumar on 14-Mar-2016 01:25

Hi All,

Could you please tell me what are the ways to find out the duplicates records in an table.

In my case I need to compare two database tables and want to find out duplicates ?

After finding out if duplicates are there i need to delete it also.

Appreciate your quick response.

All Replies

Posted by James Palmer on 14-Mar-2016 02:52

What have you tried so far? What constitutes a duplicate record?

Sent from my Windows Phone

Posted by vignesh kumar on 14-Mar-2016 03:06

My query looks like this,

FOR EACH clm130 where trndat >= 01/29/2016 and trndat <= 02/28/2016 and releas = yes NO-LOCK:

For first acm001 where acm001.trndat = clm130.trndat and

                      acm001.curcod = clm130.curcod and

                      acm001.recno = clm130.claim  and

                      acm001.syid = clm130.syid and

                      acm001.trnty1 = clm130.trnty1 and

                      acm001.branch = clm130.branch and

                      acm001.docno <> clm130.docno No-Lock :

Posted by Marco Mendoza on 14-Mar-2016 09:18

"acm001.recno = clm130.claim  and" ???

Posted by vignesh kumar on 15-Mar-2016 00:19

Field name was different but both will have same values only.

docno was the only difference between two tables based on that i need to find duplicates as all other fields are same.

Posted by gus on 15-Mar-2016 09:16

take a look at the BUFFER-COMPARE statement in the 4GL reference manual


gus (

"A distributed system is one in which the failure of a computer you didn't

even know existed can render your own computer unusable."

-- Leslie Lamport

This thread is closed