Hi John!

Having one sql statement and then put some other sql statement around
that sql statement quickly become complex, and should be done with great
care. I don't know whether your statement should work or not, but if
you're trying to do what I think you're trying to do, it can certainly
be simplified considerably:

delete from zaras_futar_forgalmak z1
where exists(
select * from zaras_futar_forgalmak z2
where z2.datum = z1.datum
and z2.id > z1.id)

This should be a lot quicker than your original sql, it only has to do
2000-5000 lookups in your table - your sql did at least a few millions!

However, it may be slightly different from your original sql: It will
delete all records except the one with the highest id for every datum
whereas you only seem to delete the lowest id. There won't be any
difference if there are two records on the same date, but with three
records, the above sql deletes two of them - your original sql seems to
only delete one.

If you really wanted to only delete one in case of three duplicates (a
very unusual requirement), use

delete from zaras_futar_forgalmak z1
where exists(
select * from zaras_futar_forgalmak z2
where z2.datum = z1.datum
and z2.id > z1.id)
and not exists(
select * from zaras_futar_forgalmak z3
where z3.datum = z1.datum
and z3.id < z1.id)

HTH,
Set

nagypapi wrote:

> Maybe this is outdated:
>
> I am using fbserver1.5.2 on a winxp machine, and was browsing through
> my database.
>
> The following sql statement worked perfectly in a few seconds, and
> gave back 1 result:
> select min(id) from zaras_futar_forgalmak where datum in
> (select datum from zaras_futar_forgalmak group by datum
> having count(distinct id)>1)
>
> but the following caused a 100% CPU load:
> delete from zaras_futar_forgalmak where id in
> (select min(id) from zaras_futar_forgalmak where datum in
> (select datum from zaras_futar_forgalmak group by datum
> having count(distinct id)>1)
> )
>
>
> The table ddl:
> CREATE TABLE ZARAS_FUTAR_FORGALMAK (
> ID BIGINT,
> PERSONEL_ID BIGINT,
> FUTAR_NEV VARCHAR(50),
> CIMEK INTEGER,
> FUTAR_FORGALOM BIGINT,
> KUPON_DB INTEGER,
> KUPON_FT BIGINT,
> NEM_VETTEK_AT BIGINT,
> SZAMLAK BIGINT,
> ETKEZESI_JEGYEK BIGINT,
> TULORA_CIMEK INTEGER,
> FELAR INTEGER,
> DATUM TIMESTAMP,
> UTOLAG_ADOTT_KEDVEZMENY DOUBLE PRECISION
> );
>
> The table has approximately 2000-5000 records
>
> I waited a few minutes, but it didn't finish, should I have waited more?
>
> John