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?
