Delete Mutiple entries that are the same (MS SQL)

[Tryst]

Hi all,

If I have a table that contains multiple data that is the same across all columns apart from the primary key, how can I delete all these multiple entries but just leave one entry in the table for each set of identical multiple entries?

Thanks

Tryst

[r937]

DELETE FROM yourtable
WHERE somecolumn=somevalue
AND someothercolumn=someothervalue
AND primarykey <> value_of_row_you_want_to_keep

[Tryst]

but is there an automated way of getting rid of many occurances where the columns are the same, for example there could be 20 rows where the columns are the same, and then followed by another set of columns where the values are different from the last 20 columns but the values are the same for the next 30 columns etc etc.

Or do I have to do it manually through a query like the one you have given?

Thanks

Tryst

[r937]

no, you do not have to do it manually, but you have given us blessed little to go on

[Tryst]

Ok, this is what I meant

id - col1 - col2 
1  - xxxx - xxxx
2  - xxxx - xxxx
3  - xxxx - xxxx
4  - yyyy - yyyy
5  - yyyy - yyyy
6  - yyyy - yyyy
7  - mmm - mmm
8  - mmm - mmm
9  - mmm - mmm

So is there a way that all those duplicate rows can be deleted in one query? Or do I have to run a query for each batch of rows that are identical?

Thanks

Tryst

[r937]

all those duplicate rows, or all but one? and if all but one, how do you determine which one to keep?

[Tryst]

Its ok, r937, its sorted now.

I had to run a query that had the DISTINCT keyword in it to prevent the data be populated teh way I had described.

Thanks

Tryst