Post reply

Delete duplicate rows in a Table?

  • April 12, 2010 at 1:07 pm

    #1150288

    When I have this problem, I "cheat" if I can.

    I create a temp table, with a unique index on the column containing duplicates.

    Then set "ignore duplicates" on the index.

    Then copy from your live table containing duplicates into the temp table. It will only allow one record and delete the others.

    Then truncate the live data & copy back in from the temp table.

    This may not work if you have huge tables, or cannot truncate your live table for a variety of reasons, but it is an option.

    (Hey, duplicate threads about duplicate records !:-D)

  • April 13, 2010 at 3:24 am

    #1150562

    Hi,

    Try with this query

    Delete from [dbo].[folders_rel] where id not in

    (Select Min(id) from [dbo].[folders_rel]

    group by folder_id, polymorphic_id)

    I hope this works. If this is incorrect kindly let me know.

    thanks,

    niranjan.

  • April 14, 2010 at 7:54 pm

    #1151848

    Unlike Lutz's sample table in his previous post, Raja's table does have a unique key. Using that and set-based logic the following script works for me, regardless of the number of duplicates

    DECLARE @folders TABLE (

    [id] [varchar](36) NOT NULL,

    [folder_id] [varchar](36) NOT NULL,

    [polymorphic_module] [varchar](25) NOT NULL,

    [polymorphic_id] [varchar](36) NOT NULL,

    [deleted] [bit] NULL DEFAULT ('0'),

    PRIMARY KEY CLUSTERED ([id] ASC));

    INSERT @folders

    ([id], [folder_id], [polymorphic_module], [polymorphic_id], [deleted])

    VALUES ('1', 'Aug', 'Folder', 'ed1', '0');

    INSERT @folders

    ([id], [folder_id], [polymorphic_module], [polymorphic_id], [deleted])

    VALUES ('2', 'Aug', 'Folder', 'ed2', '0');

    INSERT @folders

    ([id], [folder_id], [polymorphic_module], [polymorphic_id], [deleted])

    VALUES ('3', 'Sep', 'Folder', 'ed1', '0');

    INSERT @folders

    ([id], [folder_id], [polymorphic_module], [polymorphic_id], [deleted])

    VALUES ('4', 'Sep', 'Folder', 'ed3', '0');

    INSERT @folders

    ([id], [folder_id], [polymorphic_module], [polymorphic_id], [deleted])

    VALUES ('5', 'Aug', 'Folder', 'ed1', '0');

    INSERT @folders

    ([id], [folder_id], [polymorphic_module], [polymorphic_id], [deleted])

    VALUES ('6', 'Aug', 'Folder', 'ed1', '0');

    INSERT @folders

    ([id], [folder_id], [polymorphic_module], [polymorphic_id], [deleted])

    VALUES ('7', 'Sep', 'Folder', 'ed3', '0');

    SELECT * FROM @folders;

    DELETE @folders

    FROM @folders A

    LEFT OUTER JOIN (SELECT MIN([id]) [id]

    ,[folder_id]

    ,[polymorphic_id]

    FROM @folders

    GROUP BY [folder_id]

    ,[polymorphic_id] ) B

    ON A.[id] = B.[id]

    WHERE B.[id] IS NULL;

    SELECT * FROM @folders;

    Apologies, only noticed Niranjan's post after I had posted this. For larger tables would prefer to use LEFT OUTER JOIN rather than NOT IN

Viewing 3 posts - 16 through 17 (of 17 total)

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