2

I have a table and need to delete entire row where ID occurs second and subsequent times, but leave the first occurrence of suCustomerIDBy the way. M table has ID which is a primary key and CustometID which is duplicated. So I need to remove all rows with duplicated CustomerID.

Delete From Table1 where ID IN (select ID From Table1 where count(distinct CutomerID) >=2 group by CustomerID)

The code above will delete all id including the first occurrence of each of the IDs, but I need to keep their first occurrence. Please advise.

Improve this question
3
  • 1
    Do you have another column that is unique? Commented May 13, 2016 at 21:54
  • How do you know which occurance is first? Commented May 13, 2016 at 22:03
  • Yes, there is a row_id which is a primery key Commented May 13, 2016 at 22:52

2 Answers 2

Reset to default
2

This code should give you what you need.

There may be better ways to do it if you can provide the full table schema for Table1

If you obtain the row number and then just ignore the first ones:

;WITH cte AS ( SELECT ID, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS Rn FROM [Table1] ) DELETE cte WHERE Rn > 1
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you. I'll try to adjyst it to my particular need and will accept your answer
0 
delete a from( Select dense_rank() OVER(PARTITION BY id ORDER BY id) AS Rn,* from Table1)a where a.Rn>1
Improve this answer

1 Comment

Although this code may answer the question, providing additional context regarding why and/or how it answers the question would significantly improve its long-term value. Please edit your answer to add some explanation.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.