Delete Duplicates from table with no identity column

  • Here is a sample of the table with 4 columns

    ANTONNULL05023555-3932

    ANTONNULL05023555-3932

    AROUTNULL04423555-7788

    AROUTNULL04423555-7788

    BERGSNULL07623123-5665

    BERGSNULL07623123-5665

    BLAUSNULL68306084-5660

    how would i delete duplicates from this table.

    I know some delete queries but they only work for tables with identity columns but this has all varchar fields.

    Can you help me find how to delete duplicates from this

  • How about:

    SELECT dupeColumn,

    COUNT(dupeColumn) AS NumOccurrences

    FROM users

    GROUP BY dupeColumn

    HAVING ( COUNT(dupeColumn) > 1 )

    --
    :hehe:

  • This deletes all the columns from the table

  • you need to use the ROW_NUMBER() function

    by using the ROW_Number function every row will have a different row id.

    I personally never used it but I saw my ex-colleague use it.

  • ar-727381 (9/25/2009)


    This deletes all the columns from the table

    I'm not sure I understand. It deletes all columns from the table? :crazy:

    --
    :hehe:

  • ok here's a 2005 secret: there is a hidden row identifier in every table row named %%LockRes%%,

    in 2008 they renamed the identifier to %%physloc%%

    you can use that as part of a delete statment:

    Create Table myHeap(HeapName varchar(30),SomeNullColumn int,SomeId int,phone varchar(8))

    INSERT INTO myHeap

    SELECT 'ANTON',NULL,05023,'555-3932' UNION ALL

    SELECT 'ANTON',NULL,05023,'555-3932' UNION ALL

    SELECT 'AROUT',NULL,04423,'555-7788' UNION ALL

    SELECT 'AROUT',NULL,04423,'555-7788' UNION ALL

    SELECT 'BERGS',NULL,07623,'123-5665' UNION ALL

    SELECT 'BERGS',NULL,07623,'123-5665' UNION ALL

    SELECT 'BLAUS',NULL,68306,'084-5660'

    select

    %%LockRes%%,

    myHeap.*

    from myHeap

    /*

    --results:

    LockRes HeapName SomeNullColumn SomeId phone

    1:35564:0 ANTON NULL 5023 555-3932

    1:35564:1 ANTON NULL 5023 555-3932

    1:35564:2 AROUT NULL 4423 555-7788

    1:35564:3 AROUT NULL 4423 555-7788

    1:35564:4 BERGS NULL 7623 123-5665

    1:35564:5 BERGS NULL 7623 123-5665

    1:35564:6 BLAUS NULL 68306 084-5660

    */

    --delete works! note that the identifier was unique to my machine...yours will generate a different value!

    delete from myHeap where %%LockRes%% ='1:35564:0'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • This is great to know...

    I've used the previous post to delete all duplicates....

    Thanks

    Select min(%%LockRes%%) as ident,* into #test from MyHeap group by HeapName, SomeNullColumn, SomeId, phone

    delete from M

    from MyHeap M

    left join #test T on T.ident = M.%%LockRes%%

    where T.HeapName is null

    Select * from MyHeap

    I really like this hidden feature...

  • i already have this table created. i just gave you the top few rows of it. I dont think i can update the table with %%LockRes%%

    so will need some more help to achieve deletion of duplicates.

    by the way. it was nice to know about the hidden feature. thanks a lot

  • ar-727381 (9/25/2009)


    i already have this table created. i just gave you the top few rows of it. I dont think i can update the table with %%LockRes%%

    so will need some more help to achieve deletion of duplicates.

    by the way. it was nice to know about the hidden feature. thanks a lot

    ALL tables, including the one you created, have this....it's just hidden:

    try SELECT %%LockRes%%,* FROM YOURTABLE

    you'll see it exists, and so you could adapt our example solutions to do the same.

    jghali 's solution is so clean and simple, I would highly recommend it...just change the group columns to your real columns.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • try this and give me feedback:

    with duplikate as (

    SELECT row1,row2,row3,

    row_Number () Over (partition by row1,row2,row3 order by row1) -- sortierung ist eigentlich egal

    as RowNumber

    FROM Table

    )

    delete from duplikate

    where RowNumber >=2

    :hehe:

  • WOW...

    That one works too.

    I just need to try to understand it.:-)

  • Just what i was looking for...

    works perfectly

    thank you so much

  • Lowell (9/25/2009)


    ok here's a 2005 secret: there is a hidden row identifier in every table row named %%LockRes%%,

    in 2008 they renamed the identifier to %%physloc%%

    you can use that as part of a delete statment:

    Create Table myHeap(HeapName varchar(30),SomeNullColumn int,SomeId int,phone varchar(8))

    INSERT INTO myHeap

    SELECT 'ANTON',NULL,05023,'555-3932' UNION ALL

    SELECT 'ANTON',NULL,05023,'555-3932' UNION ALL

    SELECT 'AROUT',NULL,04423,'555-7788' UNION ALL

    SELECT 'AROUT',NULL,04423,'555-7788' UNION ALL

    SELECT 'BERGS',NULL,07623,'123-5665' UNION ALL

    SELECT 'BERGS',NULL,07623,'123-5665' UNION ALL

    SELECT 'BLAUS',NULL,68306,'084-5660'

    select

    %%LockRes%%,

    myHeap.*

    from myHeap

    /*

    --results:

    LockRes HeapName SomeNullColumn SomeId phone

    1:35564:0 ANTON NULL 5023 555-3932

    1:35564:1 ANTON NULL 5023 555-3932

    1:35564:2 AROUT NULL 4423 555-7788

    1:35564:3 AROUT NULL 4423 555-7788

    1:35564:4 BERGS NULL 7623 123-5665

    1:35564:5 BERGS NULL 7623 123-5665

    1:35564:6 BLAUS NULL 68306 084-5660

    */

    --delete works! note that the identifier was unique to my machine...yours will generate a different value!

    delete from myHeap where %%LockRes%% ='1:35564:0'

    Nice.. this is interesting stuff... is it always unique though? And what's allowed using this? Updates, Inserts, Deletes?

    I'm not so sure if I could permanently use this as an identity column, but it definitely would be extremely helpful in certain situations.....

    Awesome information. Thanks.

    --
    :hehe:

  • it's the actual physical address: i think the format is [file_id]:[page_id]:[slot_id], so it's the true physical address on your server;

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • jghali (9/25/2009)


    WOW...

    That one works too.

    I just need to try to understand it.:-)

    if you have further question or need help ,don´t hesitate to ask here 😎

Viewing 15 posts - 1 through 15 (of 23 total)

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