You can use below query to delete identical or same or duplicate entries from table:
1) Delete from table:
DELETE T FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS ID
FROM tbl_Test
WHERE Id='1' ) T
WHERE ID > 1
2) Delete from table having join with other tables
DELETE tbl_Test FROM (
SELECT ROW_NUMBER() OVER (ORDER BY T.Id) AS ID ,T.Id
FROM tbl_Test T
INNER JOIN tbl_Join J on J.Id = T.Id
WHERE T.Id='1' ) R
WHERE R.ID > 1
1) Delete from table:
DELETE T FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS ID
FROM tbl_Test
WHERE Id='1' ) T
WHERE ID > 1
2) Delete from table having join with other tables
DELETE tbl_Test FROM (
SELECT ROW_NUMBER() OVER (ORDER BY T.Id) AS ID ,T.Id
FROM tbl_Test T
INNER JOIN tbl_Join J on J.Id = T.Id
WHERE T.Id='1' ) R
WHERE R.ID > 1
This blog was helpful :)
ReplyDeleteUsed below query to delete rows when primary is a combination of 2 columns:
DELETE T FROM (SELECT ROW_NUMBER() OVER (PARTITION BY column1,column2 ORDER BY column1,column2) AS ID FROM tst_tbl ) T WHERE ID > 1
Thanks a lot again :)
ReplyDelete