You should be able to do it with the select you already have, something like this:
delete ACCOUNTACTION from ACCOUNTACTION join ( 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)) ) as duprecs ON ACCOUNTACTION.ID=duprecs.ID Almost any select statement can be turned into a delete statement. The tables before FROM will have records deleted and the tables after FROM will be used as a filter. You could actually delete from multiple tables in the same query. Hope that helps. Brent Baisley On Wed, Aug 6, 2008 at 4:31 AM, 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? > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
