Up until now, the way I understood to "fix" a table with duplicate data and no primary key or unique key is either:
-- test delete duplicate rows from table without changing table
CREATE TABLE dbo.DeleteDupFromViewTest
(FirstName VARCHAR(100) NOT NULL
, LastName VARCHAR(100) NOT NULL)
GO
INSERT INTO dbo.DeleteDupFromViewTest
(FirstName, LastName)
VALUES
('FirstNameOne', 'LastNameOne')
, ('FirstNameTwo', 'LastNameTwo')
, ('FirstNameThree', 'LastNameThree')
, ('FirstNameThree', 'LastNameThree')
, ('FirstNameThree', 'LastNameThree')
, ('FirstNameOne', 'LastNameOne')
, ('FirstNameTwo', 'LastNameTwo')
, ('FirstNameTwo', 'LastNameTwo')
, ('FirstNameThree', 'LastNameThree')
, ('FirstNameThree', 'LastNameThree')
, ('FirstNameOne', 'LastNameOne')
GO
CREATE VIEW dbo.vw_DeleteDupFromViewTest
AS
SELECT
FirstName
, LastName
, ROW_NUMBER() OVER(ORDER BY FirstName, LastName) AS RowID
FROM dbo.DeleteDupFromViewTest
GO
SELECT * FROM dbo.vw_DeleteDupFromViewTest
DELETE t1
FROM dbo.vw_DeleteDupFromViewTest t1
LEFT OUTER JOIN (SELECT
FirstName
, LastName
, MIN(RowID) AS RowID
FROM dbo.vw_DeleteDupFromViewTest (NOLOCK)
GROUP BY FirstName
, LastName
) t2
ON t1.FirstName = t2.FirstName
AND t1.LastName = t2.LastName
AND t1.RowID = t2.RowID
WHERE t2.FirstName IS NULL
SELECT * FROM dbo.vw_DeleteDupFromViewTest
SELECT * FROM dbo.DeleteDupFromViewTest
DROP VIEW dbo.vw_DeleteDupFromViewTestDROP TABLE dbo.DeleteDupFromViewTest
Screenshot of result:
