Post reply

How to delete the duplicate row without any unique value? please help

  • April 26, 2013 at 9:40 am

    #394678

    I have a table of contacts that has no unique key on it. The users have entered multiple copies of several people's information and I want to remove the duplicate values.

    Here are the column names

    Last_Name, First_NAme, Department, Email, phone, Sid (sid can be null)

    Please help-

  • April 26, 2013 at 9:45 am

    #1610436

    ;WITH OrderedRows AS (

    SELECT Last_Name, First_NAme, Department, Email, phone, Sid,

    rn = ROW_NUMBER() OVER(PARTITION BY Last_Name, First_NAme, Department, Email, phone, Sid ORDER BY (SELECT NULL))

    FROM MyTable

    )

    DELETE FROM OrderedRows WHERE rn > 1

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • April 26, 2013 at 10:40 am

    #1610461

    Thank you so much and God bless you! TGIF

  • April 26, 2013 at 10:59 am

    #1610465

    hydbadrose (4/26/2013)

    Thank you so much and God bless you! TGIF

    You're welcome and Happy Friday to you too!

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply