🤩 New Cool Developer Tools for you. Explore →
FREE JavaScript Video Series Start Learning →
FLAT 75% OFF All Interactive courses at flat ₹250 / $3.25 only. HURRRRRY!! Explore now
  Signup/Sign In
Tests
MCQs to test your knowledge.
Forum
Engage with the community.
Compilers
Compilers to execute code in browser.
← Back to Questions
Ask Question
Not satisfied by the Answer? Still looking for a better solution?
Ask Question

How to delete duplicate rows in SQL Server?

How can I delete duplicate rows where no unique row id exists?

My table is

col1  col2 col3 col4 col5 col6 col7

john  1    1    1    1    1    1 

john  1    1    1    1    1    1

sally 2    2    2    2    2    2

sally 2    2    2    2    2    2


I need to be left with the following after the duplicate removal:
john  1    1    1    1    1    1

sally 2    2    2    2    2    2


I've tried a few questions but I guess they depend on becoming a row id as I don't get the wanted result. For example:
DELETE

FROM table

WHERE col1 IN (

    SELECT id

    FROM table

    GROUP BY id

    HAVING (COUNT(col1) > 1)

)
sql-delete sql
by

3 Answers

espadacoder11
Without using CTE and ROW_NUMBER() you can just delete the records just by using group by with MAX function here is and example


DELETE

FROM MyDuplicateTable

WHERE ID NOT IN

(

SELECT MAX(ID)

FROM MyDuplicateTable

GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)
kshitijrana14 
DELETE from search

where id not in (

   select min(id) from search

   group by url

   having count()=1



   union



   SELECT min(id) FROM search

   group by url

   having count() > 1

)
RoliMishra
If you have no references, like foreign keys, you can do this. I do it a lot when testing proofs of concept and the test data gets duplicated.

SELECT DISTINCT [col1],[col2],[col3],[col4],[col5],[col6],[col7]



INTO [newTable]



FROM [oldTable]


Go into the object explorer and delete the old table.

Rename the new table with the old table's name.

Login / Signup to Answer the Question.

LoginSignup