Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Question Unanswered: SQL Server: Deleting Duplicate Records

    I have a table with lots of records. I just realized that I can reduce the
    size greatly, by deleting the duplicate records.
    There are four columns (MPN, Manufacturer, SupplierName, ResponseDate) which verify the uniqueness of each record.
    I have to set those columns as primary keys, but I need to get rid
    of the duplicates first.
    I know that I have to copy the unique records to another table, but
    I don't know how.
    Your genius answer is very much appreciated!

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: SQL Server: Deleting Duplicate Records

    Originally posted by liberator28
    I have a table with lots of records. I just realized that I can reduce the
    size greatly, by deleting the duplicate records.
    There are four columns (MPN, Manufacturer, SupplierName, ResponseDate) which verify the uniqueness of each record.
    I have to set those columns as primary keys, but I need to get rid
    of the duplicates first.
    I know that I have to copy the unique records to another table, but
    I don't know how.
    Your genius answer is very much appreciated!
    Select below will return duplicates:

    select MPN, Manufacturer, SupplierName, ResponseDate
    from youtable
    group by MPN, Manufacturer, SupplierName, ResponseDate
    having count(*)>1

  3. #3
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    63
    Hi there,

    I would suggest to create a second table, that has got the same structure as the original, but with an additional column at the end. This column stores an identity and may be defined like this:

    [Unique_ID] [bigint] IDENTITY (1, 1) NOT NULL

    Then, insert all records of the original table into the new table with something like

    insert into YourNewTable
    select *
    from YourTable

    Afterwards you can select all duplicates except of the one with the lowest UNIQUE_ID with

    select t1.MPN, t1.Manufacturer, t1.SupplierName, t1.ResponseDate, unique_id
    from YourNewTable t1
    where unique_id not in (select min(unique_id)
    from YourNewTable t2
    where t2.MPN = t1.MPN
    and t2.Manufacturer = t1.Manufacturer
    and t2.SupplierName = t1.SupplierName
    and t2.ResponseDate = t1.ResponseDate)


    So, you have created your own key and know exactly which records to delete.

    Hope this helps further.

    Greetings,
    Carsten
    Last edited by CarstenK; 10-03-03 at 09:37.

  4. #4
    Join Date
    Mar 2003
    Location
    Bucharest, Romania
    Posts
    80
    You may use one of the following methods

    First method (4 steps)
    1) select the data you need into a new table (make-table query)
    select distinct MPN, Manufacturer, SupplierName, ResponseDate
    into NewTable
    from yourtable

    2) Delete the old table (yourtable)

    3) Rename the NewTable as yourtable

    4) If the late your table had any indexes, constraints and/or was implied in relations with other tables in your database you should re-create them.


    Second (also four steps)
    1) the same like in the first method

    2) delete from yourtable

    3) insert into your table select * from NewTable

    4) delete the NewTable


    PS
    If you don't want your transaction log to be filled up with lots of data you should go with the first method, otherwise go with the second one


    ionut
    Who cares about Romanian football ???

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •