Deleting Duplicate Records 1

[punky001]

I have a SQL table that has an idential duplicate record and I am unable to delete one of them. I get the following error message:
"The row value(s) updated or deleted wither do not make the row unique or they alter multiple rows".

Any idea why I can't delete one (or both) of these records?

 

[r937]

does the table have a primary key?

if not, it should

if it does, then you cannot possibly have a complete duplicate row


more information, please

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

You must have a primary key for all tables in your database.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Qik3Coder]

@bborissov, you "should" have, not you "must" have. If must have, we wouldn't have this discussion, like evers.


@punky,
Need the delete statement and some sample data (2-10 rows)
You should obscuficate any important data, and please no cc data.

Thanks!
Lodlaiden

I'll answer your question, not solve your problem

 

[bborissov]

Thanks.
But I really meant "must" not "should"
Just because this is mandatory for me

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[r937]

qikcoder, even if the table has a primary key, i have seen the "Halp!! How do I remove duplicates??" thread way too many times to really believe that we would never have this conversation

people assign IDENTITY keys all over da place, and neglect to declare a UNIQUE constraint on at least one other candidate key, and then wonder how to get out of the corner they've painted themselves into...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Qik3Coder]

@bborissov,
I won't create a table without a PK either...

@r937
I agree with you. I was merely indicating that if the table must have a primary key, this at least gives you something to hook to for the deletes.

Usually there is some constraint to keep one of the duplicate rows. My vanilla answer is some variant on:

Create a select with a row_number() and delete row_num > 1

Lodlaiden




I'll answer your question, not solve your problem

 

[SQLBill]

One option is to copy the row into a temporary table. Then drop the duplicate rows. Lastly, put the single row back in.

Another option is to add a new column using IDENTITY. Then find the duplicate rows and use the IDENTITY column to drop one of them. You can then remove the IDENTITY column and if possible make a primary key column so you don't have the issue in the future.


-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[punky001]

Thanks to all for responding....

SQLBill - tried your suggestion to add new column to serve as an identity column....also made a primary key. Worked like a champ!!!

Thanks again.