I doubt the belwo sql will give you duplcates select ACCOUNTACTION.ID <http://accountaction.id/> from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) > 1 and ACCOUNTACTION.ID<http://accountaction.id/>!= min(ACCOUNTACTION.ID <http://accountaction.id/>));
The reason being, for duplicates records accountaction.id will always equal to min(accountaction.id). try this select ACCOUNTACTION.ID <http://accountaction.id/> from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) > 1 or select ACCOUNTACTION.ID <http://accountaction.id/> from ACCOUNTACTION where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' group by ACCOUNTACTION.ACCOUNT_ID having (count(ACCOUNTACTION.ACCOUNT_ID) > 1 and ACCOUNTACTION.ID<http://accountaction.id/> =min(accountaction.id); I would use the first select statement. On 8/6/08, Magnus Smith <[EMAIL PROTECTED]> wrote: > > I have the following two tables > > ACCOUNTACTION > +-------------------+--------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +-------------------+--------------+------+-----+---------+-------+ > | ID | bigint(20) | NO | PRI | | | > | AccountActionType | varchar(31) | YES | | NULL | | > | DESCRIPTION | varchar(255) | YES | | NULL | | > | ACTIONDATE | datetime | YES | | NULL | | > | ACCOUNT_ID | bigint(20) | YES | MUL | NULL | | > +-------------------+--------------+------+-----+---------+-------+ > > and > > ACCOUNTPAYMENTACTION > +---------------+------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +---------------+------------+------+-----+---------+-------+ > | ID | bigint(20) | NO | PRI | | | > | AMOUNTINPENCE | bigint(20) | YES | | NULL | | > +---------------+------------+------+-----+---------+-------+ > > ACCOUNTPAYMENTACTION shares the primary key with ACCOUNTACTION > > I need to remove duplicate entries that occured at a specific time in > ACCOUNTACTION I then plan to remove the rows in ACCOUNTPAYMENTACTION > that are no longer referenced in ACCOUNTACTION by using an outer join > > I can select the duplicate records in ACCOUNTACTION using > > select ACCOUNTACTION.ID from ACCOUNTACTION > where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' > group by ACCOUNTACTION.ACCOUNT_ID > having (count(ACCOUNTACTION.ACCOUNT_ID) > 1 and ACCOUNTACTION.ID != > min(ACCOUNTACTION.ID)); > > I am trying to delete these records but am having trouble with the sql > delete > > I tried the following but nothing happened > > delete ACCOUNTACTION where ACCOUNTACTION.ID in > (select ACCOUNTACTION.ID from ACCOUNTACTION > where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' > group by ACCOUNTACTION.ACCOUNT_ID > having (count(ACCOUNTACTION.ACCOUNT_ID) > 1 and ACCOUNTACTION.ID != > min(ACCOUNTACTION.ID))); > > Can anyone help me? >
