Duplicated records
in [General]
|
Prev: hello how are you
Next: Links are not woring
From: Pietro on 6 Jul 2008 03:29 Hi, I've a problem in my database,sometimes,due to the several equationas and codes i'm using,records are duplicated,how can i run a delete query to delete records that have the same value of the fields "SR" and "Received",I need to keep one reocrd only and delete all the other repeated records.
From: david on 6 Jul 2008 06:44 Make a back up copy of the database first. The table must have a primary key. The table must have a numeric index. If the primary key is not a numeric index, add a numeric index to the table. Make a select query that joins the table to another copy of the same table. Join all the duplicate records. Then delete all the records where the index on the joined table is greater than the index on the first copy of the table (david) "Pietro" <Pietro(a)discussions.microsoft.com> wrote in message news:BE850910-5D6E-4CCD-BEB6-C9C90277F3F2(a)microsoft.com... > Hi, > > I've a problem in my database,sometimes,due to the several equationas and > codes i'm using,records are duplicated,how can i run a delete query to delete > records that have the same value of the fields "SR" and "Received",I need to > keep one reocrd only and delete all the other repeated records.
From: Ken Sheridan on 6 Jul 2008 07:20 Firstly the table must have a primary key column or an otherwise uniquely indexed column. If not add an autonumber column, MyID say. If the primary key is a multi-column one then it can still be done, but its simpler with a single column, so in this case add the autonumber column temporarily; you can delete it again later. Then execute the following Delete query: DELETE * FROM YourTable As T1 WHERE EXISTS (SELECT * FROM YourTable AS T2 WHERE T2.SR = T1.SR AND T2.Received = T1.Received AND T2.MyID < T1.MyID); Be sure to back-up the table first. Ken Sheridan Stafford, England "Pietro" wrote: > Hi, > > I've a problem in my database,sometimes,due to the several equationas and > codes i'm using,records are duplicated,how can i run a delete query to delete > records that have the same value of the fields "SR" and "Received",I need to > keep one reocrd only and delete all the other repeated records.
From: Pietro on 6 Jul 2008 09:06 Thank you Ken for your reply... The below query does not work, "Ken Sheridan" wrote: > Firstly the table must have a primary key column or an otherwise uniquely > indexed column. If not add an autonumber column, MyID say. If the primary > key is a multi-column one then it can still be done, but its simpler with a > single column, so in this case add the autonumber column temporarily; you can > delete it again later. Then execute the following Delete query: > > DELETE * > FROM YourTable As T1 > WHERE EXISTS > (SELECT * > FROM YourTable AS T2 > WHERE T2.SR = T1.SR > AND T2.Received = T1.Received > AND T2.MyID < T1.MyID); > > Be sure to back-up the table first. > > Ken Sheridan > Stafford, England > > "Pietro" wrote: > > > Hi, > > > > I've a problem in my database,sometimes,due to the several equationas and > > codes i'm using,records are duplicated,how can i run a delete query to delete > > records that have the same value of the fields "SR" and "Received",I need to > > keep one reocrd only and delete all the other repeated records. >
From: Ken Sheridan on 6 Jul 2008 11:18
That doesn't give me much of a clue. In what way is it not working? Is it reporting an error, and if so what, or just not doing what you expect? You have, I assume, changed 'YourTable' in the query to the actual name of your table? It should then work if: 1. The table has columns named SR and Received. 2. The table has a primary key or otherwise uniquely indexed column named MyID. If its named something else then you'll also need to change 'MyID' in the query to the actual name. 3. The table contains subsets of rows where the values in the SR and Received columns are the same in all rows in the subset, and you want to delete all but one row from each subset. Ken Sheridan Stafford, England "Pietro" wrote: > Thank you Ken for your reply... > The below query does not work, > > "Ken Sheridan" wrote: > > > Firstly the table must have a primary key column or an otherwise uniquely > > indexed column. If not add an autonumber column, MyID say. If the primary > > key is a multi-column one then it can still be done, but its simpler with a > > single column, so in this case add the autonumber column temporarily; you can > > delete it again later. Then execute the following Delete query: > > > > DELETE * > > FROM YourTable As T1 > > WHERE EXISTS > > (SELECT * > > FROM YourTable AS T2 > > WHERE T2.SR = T1.SR > > AND T2.Received = T1.Received > > AND T2.MyID < T1.MyID); > > > > Be sure to back-up the table first. > > > > Ken Sheridan > > Stafford, England > > > > "Pietro" wrote: > > > > > Hi, > > > > > > I've a problem in my database,sometimes,due to the several equationas and > > > codes i'm using,records are duplicated,how can i run a delete query to delete > > > records that have the same value of the fields "SR" and "Received",I need to > > > keep one reocrd only and delete all the other repeated records. > > |