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

Ayan Basu gravatar image

Ayan Basu
22 3 3 3

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

Nige gravatar image

Nige
78 1 1 2

(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

David 1 gravatar image

David 1
1.8k 1 3

GREAT !!!!!. I definitely liked !!!!! You have my vote !!!
Oct 29, 2009 at 01:00 PM Laerte Junior
(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:

x991
x476

asked: Oct 29, 2009 at 05:26 AM

Seen: 3544 times

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