x

Delete duplicate rows in table without primary key

How to delete duplicate rows in table without primary key by a single query in sql server 2000

more ▼

asked Oct 29, 2009 at 05:26 AM in Default

avatar image

Ayan Basu
22 3 3 5

how are they duplicate? the whole row or some other (non-PK) key

Oct 29, 2009 at 06:11 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I don't think you can't do this in 2000 in one statement unless you have a column in the table that can serve as a candidate key, in which case you wouldn't have duplicate rows :-). You will need to select a distinct set from the existing table to a temp table delete the existing data and reload it from the temp table. You may need to drop constraints from the table before starting, otherwise the delete will fail, and add them back afterwards.

And don't forget to be a be a good DBA and do a backup of the data first.

begin transaction

select distinct * into #temp from myTable

delete from myTable

insert into myTable select * from #temp

  • Manually check all is good, if not rollback otherwise commit

  • more ▼

    answered Oct 29, 2009 at 06:38 AM

    avatar image

    Nige
    78 2 3 5

    (comments are locked)
    10|1200 characters needed characters left

    You could do it with one delete statement: DELETE FROM tbl1;

    but only if you create this trigger first:

    CREATE TRIGGER trg1 ON tbl1 FOR DELETE AS
    INSERT INTO tbl1 SELECT DISTINCT * FROM deleted;
    

    this does assume that there are no constraints that would be violated by deleting every row.

    more ▼

    answered Oct 29, 2009 at 11:43 AM

    avatar image

    David 1
    1.8k 3 5

    (comments are locked)
    10|1200 characters needed characters left
    Your answer
    toggle preview:

    Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

    Follow this question

    By Email:

    Once you sign in you will be able to subscribe for any updates here

    By RSS:

    Answers

    Answers and Comments

    SQL Server Central

    Need long-form SQL discussion? SQLserverCentral.com is the place.

    Topics:

    x1066
    x501

    asked: Oct 29, 2009 at 05:26 AM

    Seen: 3938 times

    Last Updated: Oct 29, 2009 at 11:15 AM

    Copyright 2016 Redgate Software. Privacy Policy