deleting duplicates in a sql table

I have 7 columns.
i need rows to be deleted if there is a duplicate if all cells match between two rows on all 7 values.
is there an easy to do this?
jamesmetcalf74Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
WITH cteDistinct AS
(
	SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7,
		ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3, Col4, Col5, Col6, Col7) AS RowNumber
	FROM myTable
)
DELETE cteDistinct
WHERE RowNumber > 1

Open in new window

1
 
YZlatCommented:
you could do the following:

1) Select all distinct rows into a new table
2) truncate your original table
3) copy rows from the table you created into the original table

SELECT DISTINCT col1,col2,col3
INTO   #staging
FROM   Table1

TRUNCATE TABLE Table1

INSERT INTO Table1 (col1,col2,col3)
SELECT col1,col2,col3
FROM #staging

Open in new window

0
 
YZlatCommented:
you could also create a new regular table with the same structure, insert distinct data in that table, then delete your original table and rename new table to the same name as original table had
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Modified Brian Version  (Removed syntax errors)

--

WITH cteDistinct AS
(
	SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7,
		ROW_NUMBER() OVER(PARTITION BY Col1, Col2, Col3, Col4, Col5, Col6, Col7 ORDER BY (SELECT 1)) AS RowNumber
	FROM myTable
)
DELETE FROM cteDistinct
WHERE RowNumber > 1

Open in new window

0
All Courses

From novice to tech pro — start learning today.