Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

deleting duplicates in a sql table

Posted on 2016-09-26
4
Medium Priority
?
66 Views
Last Modified: 2016-09-28
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?
0
Comment
Question by:jamesmetcalf74
  • 2
4 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 41816877
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
 
LVL 35

Expert Comment

by:YZlat
ID: 41816884
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
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 1000 total points
ID: 41816911
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
 
LVL 35

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41817149
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question