🚀 go-pugleaf

Why not use keys, etc.?

If you don't have duplicate keys, you technically don't have duplicate
records, but duplicate data. The RDBMS has no way to determine what is
duplicate data - it doesn't care about that, only keys and metadata
structures.

To remove duplicate data you have to "roll your own" - every db is
different. You need to write a table sweeper. Usually this is based on some
knowledge you have about the database - like if you have two records in a
master table with the same social security number, it's a good bet this is a
candidate for a duplicate. That wouldn't apply to a detail table, where
duplicate SSNs might be the norm.

Obviously the brute-force approach is a one-to-all comparison of each record
in a table to every other record. If all fields except the keys match, you
have a duplicate.

The trick is to know which duplicates to delete. Is the newest one the bogus
record, or the oldest one, or what? If the keys involve any kind of sequence
or timestamp, you'll need them to know.

If you are good with SQL you can write some tasty scripts to do this stuff,
but a programmatic approach is much more straightforward. You could just do
compares with FieldByName(), but that will give you the slowest performance.
The best way to deal with duplicate data is to keep it from happening in the
first place, but that isn't always possible. The second best way is to deal
with it on the server, as a timed or otherwise triggered script on the
RDBMS, or as a daemon or service application written in C++ Builder. This
way your client application need never deal with this stuff.


"Rick Golden" <GoldenRick@hotmail.com> wrote in message
news:3c224a5d_2@dnews...
> like Jim said you can do that with SQL
> but why not constraints to assure Database  integrity????
>
>
> Jim Young <jyoung@youngendeavors.com> wrote in message
> news:3c2221d1$2_1@dnews...
> > I'm not aware of any function that does this. You could do this
> > programatically using SQL.
> >
> > Jim
> >
> > "FretBuzzz" <none@none.com> wrote in message news:3c21f9a7_2@dnews...
> > > Hi,
> > > Does anyone know of a function that will delete duplicate entries in a
> > > database table.
> > > I want to be able to achieve this without the use of keys etc.
> > > Thanks in advance,
> > > FretBuzzz
> > >
> > >
> >
> >
>
>