x

How can I find duplicates in SQL 2008

I need to find duplicate entries of the value 3 in the Status column and delete all rows containg the value of 3? I updated the rows easily but cant figure out how to find the duplicates and get rid of them. Im a rookie. Thanks

Status is my last column.

                                                        status
17657   0000200133   17714   0000200133   0000200133    3
17657   0000200133   17714   0000200133   0000200133    1
more ▼

asked Sep 20 '12 at 09:54 PM in Default

Csmiley gravatar image

Csmiley
20 1 2

I don't really get your question. The data above contains two identical rows. Do you want to delete one of them? The Status column is not three in your example data above, that's why I'm a little bit confused.
Sep 21 '12 at 05:36 AM Magnus Ahlkvist
Sorry. I picked a cloumn that had all duplicates. Lets say the status column has 10 values of the number 1 and 10 values of the number 3. I need to delete all of the values of 3 and leave the values of 1. So if the status column has any values of 3. I want to delete the entire row and leave rows only contaiing the value of 1 in the status column.
Sep 21 '12 at 11:12 AM Csmiley
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hi rookie,

I didnt get your Question clearly. Below query is useful for the remove the duplicate rows except master . I think it will useful for you.

WITH TempTable AS 
      ( 
           SELECT   Empno, row_number() OVER ( PARTITION BY Empno ORDER BY Empno) AS ID
               FROM tabEMP
       )
    Delete from TempTable  WHERE  ID > 1
more ▼

answered Sep 21 '12 at 07:29 AM

Krishjkc gravatar image

Krishjkc
50

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816

asked: Sep 20 '12 at 09:54 PM

Seen: 423 times

Last Updated: Sep 21 '12 at 11:13 AM