Delete record with higher record number

[ronwmon]

I have a table with several thousand entries. Each record has a unique record number.

I need to locate records with a duplicate field and delete the record with the higher record number.

I use the following code to locate the duplicates:

SELECT ckey, COUNT(ckey) AS Expr1
FROM AccountMainTbl0
GROUP BY ckey
HAVING (COUNT(ckey) > 1)

Would appreciate any ideas you have on how I can delete the record with the higher record number.

Thanks,

Ron

 

[bborissov]

What makes record duplicate?
ckey?
If so what is the name of the field where you keep record number?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[ronwmon]

The field name of the record number is simsrecnbr.

Ron--

 

[ronwmon]

There are many fields in the record.

The field ckey is a field that is a combination of fields needed to create a unique (primary key) for another table to identify this record. We can’t have duplicate primary key fields in the other table.

Bottom line is that I need to delete one of two records that exist in a table. The record I want to delete is the record with the higher “simsrecnbr”. I can find the two records by comparing the contents of the “ckey” field. If they are duplicates I want to delete one of the two records.

Thanks,

Ron

 

[Otto]

What about something like this:

delete from AccountMainTbl0 where simsrecnbr in
(select max(simsrecnbr) from AccountMainTbl0
  where ckey in (
SELECT     ckey, COUNT(ckey) AS Expr1
FROM         AccountMainTbl0
GROUP BY ckey
HAVING      (COUNT(ckey) > 1)
))

If a ckey is duplicated more than 2 times, you need to run the delete repeatedly.

 

[bborissov]

Try:

DELETE AccountMainTbl0
FROM AccountMainTbl0
INNER JOIN (SELECT cKey,
                   MAX(simsrecnbr) AS simsrecnbr
                   FROM AccountMainTbl0
                   GROUP BY Cnt
                   HAVING COUNT(*) > 1) Tbl1
     ON AccountMainTbl0.cKey       = Tbl1.cKey       AND
        AccountMainTbl0.simsrecnbr < Tbl1.simsrecnbr

not tested, make sure you have a good backup first.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[bborissov]

OOPS,
I just re-read your question and saw that you want to delete the record with HIGHER number. The example above will delete all records with smaller number nut the higer one. So try this:

DELETE AccountMainTbl0
FROM AccountMainTbl0
INNER JOIN (SELECT cKey,
                   MIN(simsrecnbr) AS simsrecnbr
                   FROM AccountMainTbl0
                   GROUP BY Cnt
                   HAVING COUNT(*) > 1) Tbl1
     ON AccountMainTbl0.cKey       = Tbl1.cKey       AND
        AccountMainTbl0.simsrecnbr > Tbl1.simsrecnbr

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP