Deleting multiple lignes from a table

Question

Post reply

Deleting multiple lignes from a table

jamil.tarik

Grasshopper

Points: 14
More actions
May 18, 2007 at 6:54 am

#117284

Hellow every body
I have a table with no primary key. and i would like to define a primary key (with 3 column).
the problem is that's not possible because there are a lot of lignes with the same 3 column!!
how can I keep only one of them and delete the rest?
thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65143 More actions · Answer 1

1. Extract one row for each combination of unique columns (3) to another table

2. Truncate original table

3. Insert data (1) back into original table

Far away is close at hand in the images of elsewhere.
Anon.

jamil.tarik Grasshopper Points: 14 More actions · Answer 2

how can I extract just one row for each combination??

the number of combinations is so important !!!!(about 40 000 combinations)

the table contains about 7 millions rows!!

Jus SSCrazy Points: 2536 More actions · Answer 3

Create another table with same columns + 1 additional auto increment column.

Now after transfering the data in to the new table, you can get the max of identity column for each combination with group by and then dump the result set in to the old table after truncating the old one.

Jus SSCrazy Points: 2536 More actions · Answer 4

Here goes the script for you!!

--- I have created TestDuplicates table for testing the script...

create table TestDuplicates (Col1 int,Col2 varchar(100),Col3 int)

Insert into TestDuplicates values(1,'a',1)

Insert into TestDuplicates values(2,'b',2)

Insert into TestDuplicates values(5,'e',1)

create table #TestDuplicates ([id1] [int] IDENTITY (1, 1) NOT NULL,Col1 int,Col2 varchar(100),Col3 int)

Insert into #TestDuplicates (Col1,Col2,Col3) select Col1,Col2,Col3 from TestDuplicates

select Col1,Col2,Col3,max(id1) id1 into #TestDuplicates1 from #TestDuplicates group by Col1,Col2,Col3

Truncate table TestDuplicates

Insert into TestDuplicates(Col1,Col2,Col3 )select Col1,Col2,Col3 from #TestDuplicates1

select * from TestDuplicates

drop table #TestDuplicates

drop table #TestDuplicates1