If you're good at Chinese ,just visit here. http://blog.chinaunix.net/u/29134/showart_375303.html
On Thu, Aug 7, 2008 at 10:34 PM, Magnus Smith < [EMAIL PROTECTED]> wrote: > I think got it in the end by doing a union and a join. > > delete AA, PA from ACCOUNTACTION AA, ACCOUNTPAYMENTACTION PA > where AA.ID = PA.ID and AA.ID in > (select D.ID from > > (select A1.ID from ACCOUNTACTION A1 > > left join > > ( > select * from ACCOUNTACTION A2 > where A2.ACTIONDATE like '2008-08-01 02:00%' > group by A2.ACCOUNT_ID > having count(A2.ACCOUNT_ID) > 1 > > union > > select * from ACCOUNTACTION A3 > where A3.ACTIONDATE like '2008-08-01 02:00%' > group by A3.ACCOUNT_ID having count(A3.ACCOUNT_ID) = 1 > ) as U1 > > on A1.ID = U1.ID > where A1.ACTIONDATE like '2008-08-01 02:00%' > and U1.ID is NULL > ) as D > ); > > Thanks for the pointers ;-) > > > > > -----Original Message----- > From: Magnus Smith [mailto:[EMAIL PROTECTED] > Sent: 07 August 2008 10:35 > To: Ananda Kumar > Cc: mysql@lists.mysql.com > Subject: RE: removing duplicate entries > > Yes I can see you are correct. I tried setting up a little test case > myself. > > CREATE TABLE ACCOUNTACTION ( > ID INT NOT NULL PRIMARY KEY, > ACTIONDATE DATETIME, > ACCOUNT_ID INT NOT NULL > ); > > CREATE TABLE ACCOUNTPAYMENTACTION ( > ID INT NOT NULL PRIMARY KEY, > AMOUNT INT > ); > > INSERT INTO ACCOUNTACTION (ID, ACTIONDATE, ACCOUNT_ID) > VALUES('001', '2008-08-01 02:00:00', '101'), > ('002', '2008-08-01 02:00:00', '101'), > ('003', '2008-08-01 02:00:00', '101'), > ('004', '2008-08-01 02:00:00', '102'), > ('005', '2008-08-01 02:00:00', '103'), > ('006', '2008-08-01 02:00:00', '104'), > ('007', '2008-08-01 02:00:00', '104'), > ('008', '2008-08-01 02:00:00', '105'), > ('009', '2008-08-01 03:00:00', '104'), > ('010', '2008-08-01 03:00:00', '105'), > ('011', '2008-08-01 02:00:00', '106'); > > INSERT INTO ACCOUNTPAYMENTACTION (ID, AMOUNT) > VALUES('001', '1000'), > ('002', '1000'), > ('003', '1000'), > ('004', '1000'), > ('005', '1000'), > ('006', '1000'), > ('007', '1000'), > ('008', '1000'), > ('009', '1000'), > ('010', '1000'), > ('011', '1000'); > > > I got the following query that seems to work on my test case. > > I create a union of everything that is not a duplicate and then take the > ones that are not in this to be the duplicates > > > select ACCOUNTACTION.ID, ACCOUNTACTION.ACCOUNT_ID from ACCOUNTACTION > where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' > and (ACCOUNTACTION.ID, ACCOUNTACTION.ACCOUNT_ID) > not in > (select ACCOUNTACTION.ID, ACCOUNTACTION.ACCOUNT_ID from ACCOUNTACTION > where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' > group by ACCOUNTACTION.ACCOUNT_ID > having count(ACCOUNTACTION.ACCOUNT_ID) > 1 > union > select ACCOUNTACTION.ID, ACCOUNTACTION.ACCOUNT_ID from ACCOUNTACTION > where ACCOUNTACTION.ACTIONDATE like '2008-08-01 02:00%' > group by ACCOUNTACTION.ACCOUNT_ID having count(ACCOUNTACTION.ACCOUNT_ID) > = 1); > > > The problem is that when I try to apply this to my real database tables > that are quite large then the query does not return. > > I am thinking that there must be a more efficient way to write the > query? > > I would be pleased to hear any suggestions - thanks > > > > > ________________________________ > > From: Ananda Kumar [mailto:[EMAIL PROTECTED] > Sent: 06 August 2008 13:51 > To: Magnus Smith > Cc: mysql@lists.mysql.com > Subject: Re: removing duplicate entries > > > I just did a test case here > > > select * from amc_25; > +------+ > | id | > +------+ > | 2 | > | 14 | > | 1 | > | 2 | > +------+ > 4 rows in set (0.01 sec) > > select id from amc_25 group by id having count(id) >1 and id!=min(id); > > Empty set (0.00 sec) > > > > It does not give me any rows. > > R u sure the rows returned, are the one you want to keep are indeed > duplicates > > > > > On 8/6/08, Magnus Smith <[EMAIL PROTECTED]> wrote: > > When I try the first suggestion (i) then I get all the 1682 > duplicate rows. The thing is that I need to keep the originals which > are the ones with the lowest ACCOUNTACTION.ID <http://accountaction.id/> > value. > > The second suggestion (ii) gives me 563 rows that are the > duplicates with the lowest ACCOUNTACTION.ID <http://accountaction.id/> > which are the ones I wish to keep > > So the ones I want to delete are the ones in (i) and not (ii) > > When I use > > > 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/> )); > > then I get 1119 rows which is all the duplicates in (i) less the > originals in (ii) > > > The problem I'm having is using this in a delete statement. > > > ________________________________ > > From: Ananda Kumar [mailto:[EMAIL PROTECTED] > Sent: 06 August 2008 10:11 > To: Magnus Smith > Cc: mysql@lists.mysql.com > Subject: Re: removing duplicate entries > > > > 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 > <http://accountaction.id/> will always equal to min(accountaction.id > <http://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 > <http://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 <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/> )); > > 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 > <http://accountaction.id/> in > (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/> ))); > > Can anyone help me? > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
