SQL Server Performance Forum – Threads Archive
Deleting Duplicate Records:
Deleting Duplicate Records: NameName_IDAshish1
Ashish1
Raj2
Mansi3
Mansi3
I just want to delete update records in the table. Can I have any query for this. Pls don#%92t suggest me export and import. Regards,
Ashish
Ashish Johri
The wise answer to this is that your table needed a primary key before it had any data. So insert the current data into a holding table, but only unique values … Then remove all rows from dupes_table … Add a primary key to dupes_table … Insert the data from the holding table into the dupes_table … And finally drop the holding table.
Thanks Adriaan. I was expecting this answer. If we have the same issue in Oracle we can handle this like below query by using rowcount. As this is not available here in MS-SQL server we have no option but to do this as suggested by Adriaan. Ashish Johri
Whatever platform you’re working on, just don’t ever allow any duplicate data to occur. This is a fake problem: it can only occur in a system that has a design flaw (or two).
With 2005 CTEs de-duping is easy
CREATE TABLE t1 (x INT NOT NULL, z INT NOT NULL ); INSERT INTO t1 (x,z) VALUES (1,1);
INSERT INTO t1 (x,z) VALUES (2,2);
INSERT INTO t1 (x,z) VALUES (2,2);
INSERT INTO t1 (x,z) VALUES (3,3); WITH t AS
(SELECT x,z,ROW_NUMBER()
OVER (PARTITION BY x,z ORDER BY x,z) AS r
FROM t1)
DELETE FROM t
WHERE r>1; SELECT x,z FROM t1; drop table t1
As long as original poster knows that this is a fix for a faulty design that needs to be corrected …
yeah but no need to create new table, insert.. etc.. Of course a PK or unique index would prevent this – but when someone asks for help in de-duping, it’s already too late
]]>