delete duplication in a table and retain one

Question

The main table is Table2 and I want to delete duplication RollID in Table1 but retaining 1 like the example below 6456 (Note: Primary Keys Table ID's are auto-incremental)

Table 1 (child table)

Table1_ID   RollID
 1          6456 
 2          6456 
 3          6459

Table2 (Parent)

Table2_ID    RollID
 1            6456 
 2            6459

How can I attain this scenario.. Your helping hand is highly appreciated...

Rahul Tripathi · Accepted Answer · 2015-04-10T14:05:13.120

0

Try this:

WITH CTE AS(
   SELECT Table1_ID, RollID,
       RN = ROW_NUMBER()OVER(PARTITION BY RollID ORDER BY RollID)
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

edited Apr 10 '15 at 14:05

answered Apr 10 '15 at 13:55

Rahul Tripathi

161,154
30
262
319

Is this legal in MS SQL? – Jake Apr 10 '15 at 14:00
I am receiving an error in Incorrect syntax near '='. – Jake Apr 10 '15 at 14:04
@Jake:- was missing a comma. Fixed that. Apologies! – Rahul Tripathi Apr 10 '15 at 14:05
1

Thanks you for the suggestion – Jake Apr 10 '15 at 14:08
@Jake:- You are welcome! – Rahul Tripathi Apr 10 '15 at 14:09
I am receiving an error View or function 'CTE' is not updatable because the modification affects multiple base tables. – Jake Apr 10 '15 at 14:13
@Jake:- Is it that you are trying to insert the record into your parent table which does not have duplicated records? – Rahul Tripathi Apr 10 '15 at 14:21
I already solved the problem.. enabled cascading...Thanks – Jake Apr 10 '15 at 14:49

score 0 · Answer 2 · answered Apr 10 '15 at 13:56

0

DELETE FROM Table1 WHERE ID NOT IN (SELECT MIN(ID) FROM Table1 GROUP BY ROLLID)

answered Apr 10 '15 at 13:56

Dmitrij Kultasev

5,173
3
43
79

Thank you for the suggestion... – Jake Apr 10 '15 at 14:10

delete duplication in a table and retain one

2 Answers2