April 12, 2010 at 1:07 pm
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
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
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