18

Possible Duplicate:
SQL - How can I remove duplicate rows?

Example this is my table:

SiteKey, Name, City

The SiteKey is auto increament, Name is different , but some times 2 Sitekey will have same City.

Example:

1, A , CityA
2, B,  CityB
3, C,  CityA
4, D,  CityF

so I need to remove rows 3, and keep rows 1,2,4 only.

its on SQL 2005 and up.

thanks your help.

Community
  • 1
  • 1
Ken Le
  • 1,787
  • 2
  • 22
  • 34
  • 1
    Here's a link to a question which answers yours: http://stackoverflow.com/questions/18932/sql-how-can-i-remove-duplicate-rows – Tom Studee Jul 21 '11 at 16:51

2 Answers2

13

Here are 2 ways you can do it.

DELETE t
FROM 
<table> t
WHERE EXISTS 
    (SELECT 1 FROM <table> 
    WHERE t.SiteKey > SiteKey AND t.City = City)


DELETE t
FROM 
<table> t
INNER JOIN <table> t2
ON  t.City = t2.City
AND t.SiteKey > SiteKey
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
4

This is standard SQL

DELETE
   mytable
WHERE
    SiteKey NOT IN 
    (
    SELECT
       MIN(SiteKey)
    FROM
       Mytable
    GROUP BY
       City
    ) --Don't need 'KeepMe'

Then, add a unique constraint

ALTER TABLE MyTable WITH CHECK ADD
   CONSTRAINT UQ_MyTable_City UNIQUE (City)
gbn
  • 422,506
  • 82
  • 585
  • 676
  • The standard SQL is not work for me on SQL 2005 Query. – Ken Le Jul 21 '11 at 17:01
  • @Ken Le: "Not work"? How doesn't it work... – gbn Jul 21 '11 at 17:04
  • nvm , but I tried use LEFT OUTER Join and remove column have null, its work... Thanks your help tpp, gbn. Have a nice day. – Ken Le Jul 21 '11 at 17:08
  • 1
    @gbn -- It didn't work because you named your subquery in the WHERE -- just remove the 'KeepMe' and it works fine. Otherwise, nice solution, and +1 anyway for the unique constraint :-) – Chains Jul 21 '11 at 17:53