Post reply

key is not unique

  • November 24, 2004 at 3:02 pm

    #84272

    Hey

    I just got this error when I was trying to look at one of mine tables.

    The table contains more than 1 000 000 posts, this is not correct and I expect this is becuase I have some duplicate keys in the table.

    Wwhat is the most effective way to find duplicate keys?

    regards

    DJ

  • November 24, 2004 at 3:22 pm

    #531481

    From the MS Access find duplicates query wizard...

    SELECT First(dbo.tablename.key_column) AS [DUPE_ID], Count(dbo.tablename.key_column) AS NumberOfDups

    FROM dbo.table

    GROUP BY dbo.tablename.key_column

    HAVING (((Count(dbo.tablename.key_column))>1));

    Kindest Regards,

    Sean Wyatt
    seanwyatt.com

  • November 25, 2004 at 12:40 am

    #531494

    Thanks for your tip.

    Do you know if this will work whwn you have more than one key.

    I have two key`s.

    Will try your tip and hope that helps to find the duplicate.

  • November 25, 2004 at 1:36 am

    #531501

    In case it doesn't help do a search here in the script section.

    Identifying duplicates is one of the most frequently asked questions. So it's very likely you'll find a solution there.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • November 27, 2004 at 7:24 am

    #531649

    Do you want to automatically delete the dupes (leaving only the earliest or the latest) or just find them?  Frank is correct... the search leads to lot's of "duplicate row" goodies but I've got a pretty nifty way of doing it without cursors or WHILEs.  If you post the two fields you want to key on, a date field (ID field will also be OK to select the earliest or latest) and an IDENTITY field of some sort, I'll get back to you with a script.

    Also, you say there's more than a million records and that that's not correct... any idea on approximately how many there should be (for my testing/timing)?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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