SQL SERVER: How to delete duplicate rows in one table having one-to-many mapping with other

Question

I have a SQL Server containing duplicate rows w.r.t a particular column of a table tableObj which I want to delete. The query I am using is:

DELETE tableObj 
WHERE id NOT IN 
(SELECT MIN(id)
FROM tableObj
GROUP BY column_name);  

This column_name is duplicate and I want to remove all the other rows containing the same column. Now as I execute this query, i get following error:

The DELETE statement conflicted with the REFERENCE constraint "some_value". The conflict occurred in database "UH", table "Test.otherTable", column 'column_name'.

This otherTable is with which there is one-to-mapping of tableObj. Now how do I delete these references as well along with duplicate deletion.

This is how I find the duplicates:

SELECT column_name, COUNT(*)
FROM tableObj
GROUP BY column_name
HAVING 
COUNT(*) > 1

which gives me result like:

column_name   (no-specific column) 
12345              2
23414              3
...........
...........

The mapping is persisted in the mapping table like:

tableObjID         otherTableId

The desired result after executing the DELETE query is to get(COUNT=1):

column_name   (no-specific column) 
12345              1
23414              1
...........
...........

Answer 1

You don't need to delete from the otherTable, if the ON DELETE CASCADE setting enabled in the Foreign Key constraint

Otherwise you first need to delete the referenced data from other table, and then delete the main data from the table.

Following query should work in your case.

--Delete the Referenced Data
DELETE FROM otherTable
WHERE ID IN
(
  SELECT ID FROM
   ( 
    SELECT ID, ROW_NUMBER() OVER( PARTITION BY column_name ORDER BY ID) RN
    FROM tableObj
   )T WHERE T.RN>1
)
GO
--DELETING THE DUPLICATE ROWS
WITH CTE AS
(
 SELECT ID, ROW_NUMBER() OVER( PARTITION BY column_name ORDER BY ID) RN
 FROM tableObj
)
DELETE  CTE WHERE RN > 1