Can't delete duplicate row. Anyone know how?

[tygger]

This is probably a stupid question to most of you and it doesn't deal with programming, but I couldn't find a place to ask this question dealing with SQL Server.

In a table with 2 columns, I can't delete a duplicate row.

For example:

Column 1 Column 2
-----------------------------------------
Bob 703-123-4567
John 703-987-6543
Bob 703-123-4567

When I try to delete or even edit the "Bob" row, I get this error message:

"Key column information is insufficient or incorrect. Too many rows were affected by update."

Does anyone know how I can get rid of the duplicate row??

Thank you.

 

[johnherman]

Here's a plan (may be minor syntax errors):

SELECT Column1, Column2 INTO DUPLICATE_TABLE
FROM YOURTABLE

TRUNCATE YOURTABLE

INSERT INTO YOURTABLE
SELECT DISTINCT(Column1 + Column2) FROM DUPLICATE_TABLE

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[NilsBevaart]

In future, to prevent duplicate rows, try adding an identity column to your tables. This way you can always specify a specific row for selection, updates or deletes

Nils Bevaart

 

[tygger]

Thanks. I'm new to this and I'm just trying to learn my way around.

Where would I do the SQL for this type of function? Do I just open the original table (using Enterprise Manager) and type in the SQL there?

And to make a duplicate table, is there an easy way to "copy and paste" the table or do I have to create it from scratch with the same field names and characteristics?

Thanks everyone and sorry for the basic-type questions.

 

[Katy44]

To copy the entire table, open up the 'Tables' view in EM, right click somewhere in the free area and choose 'import data'. The choose to import the data in the table you want to copy. The destination it will give you is 'Results'. When that's run, if the table doesn't already exist it will create it for you (you can change the name of 'results' either while setting up the data transfer or after the table has been created.
You can run queries in EM, but you're better off running them in Query Analyzer. If you're already in your database in EM, go to Tools and SQL Query Analyzer - that will open it up for you and set the database to use the right one.

 

[tygger]

Thanks everyone. Since there was only 1 instance of a duplicate record, I just used a DELETE statement to delete both the records and inserted one back. It worked!