Unable to delete duplicate records
I was importing records via DTSWizard, and I was having problems so I turned off Enforce Replicaton, Enforce FK Constraints on a couple of fields. I'm new with SQL Server so I'm not sure if this even caused the problem. (Do I need to turn these back on, or is this a Developer switch of some kind?)
The end result left me with duplicate records in the table, and I'm not able to delete any of them. This is the Error I got...
A problem occurred attempting to delete row 1.
Error Source: Microsoft.VisualStudio,Datatools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they after multiple rows(2 rows).
If someone could tell me what I need to do so I can delete the records I'd really appreciate it.
Thanks,
Bill
[812 byte] By [
Car54] at [2008-1-6]
sp_helpconstraint TableName
find all constraints on the table with the stored procedure above. Then drop the constraints.
ALTER TABLE TableName
DROP Constraint constraintname found from above query
Adamus
With SQL Server, in order to delete a row, there must be a way to uniquely identify the row to be deleted. When completely duplicate rows occur, it becomes difficult (in normal circumstances) to delete just one of the pair.
Here are a couple of possiblilites.
Create a temp table, and make a copy of one of the rows in the temp table, delete both rows in the original table, and then add the single row from the temp table back into the original table.
OR
Add an IDENTITY column to the table. Then remove one of the duplicate rows using the new IDENTITY value. Remove the IDENTITY column if no longer needed.
Perhaps other interesting ideas will come forth...
Bill,
Since you have so many duplicate rows, the easiest method would most likely be the following:
SELECT a single copy of each row into a new table.
EITHER
DELETE the contents of the current table (or TRUNCATE the table
AND add the contents of the new table back to the original table
OR
DROP the original table
RENAME the new table to the original table
Here is how to write a query to extract a single row in the place of two duplicates.
Code Snippet
SELECT
Column1,
Column2,
{list all columns}
INTO Your NewTable
FROM Your OldTable
GROUP BY
Column1,
Column2,
{list all columns exactly like above}
Thanks for getting back with me Arnie.
I don't need any of the contents of the table, what can I do to totally delete all the rows in the table?
Thanks,
Bill
The fastest way to 'clear out' the table, deleting all contents is:
TRANCATE TABLE MyTable
IF there are PK-FK relationships with other tables, that will not be allowed, and then the next faster alternative is:
DELETE MyTable
Thanks Arnie...
I said No to Foriegn Key Contraint on one field it was on.
I ran...
TRUNCATE
xlaANLiListsSubscribers
But I'm getting this error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'xlaANLiListsSubscribers'.
Thanks,
Bill
Arnie,
Thanks for correcting my dumb mistake.
Arnie this isn't the first time you helped me out, you're the greatest... thank you very much.
Bill
That's ok Bill,
Just remember, you know more that someone else, so please drop in here occasionally and help out. Folks volunteer in a give and take arrangement.
I appreciate you kind words.
Arnie
