sql delete row error

Question

I am trying to delete a row in sql server management studio 2012 but an error appears:

sql error

No rows were deleted

A problem occurred attempting to delete row 2 Error Source: Microsoft.SqlServer.Management.DataTools Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows)

Is there a way to fix error that without typing any query?

Solution 1

You don't have a primary, unique key in your table.

SQL Server is unable to delete your row because nothing discriminate it from the other rows.

The solution is to add a unique Primary key to your table. It is not recommended to have none anyway. A simple integer with autoincrement should work transparently for you.

Solution 2

Thanks @Hani

I had the same problem (actually a table with a Unique ID, but with some rows accidentally duplicated including the "unique ID" so I couldn't delete the duplicate rows), and your advise helped me solved it from the SQL Server Management GUI.

1. I used the GUI interface to "edit top 200 rows" in table.
2. I then added a filter in the SQL Criteria pane which brought up just my two duplicate rows. (This was were I couldn't deleted one of the rows from).
3. Inspired by your comment, I opened the SQL Pane and changed the:

SELECT TOP(200)...{snip my criteria created by filter}

to instead read:

SELECT TOP(1)...{snip my criteria created by filter}

4. I was then able to "Execute SQL" the tweaked SQL.
5. I was then able to use the interface to Delete the single line shown (no warnings this time).
6. Re-running the SQL Criteria with 200 rows confirmed that just one row had been successfully deleted and one remained.

Thanks for the help, this proved to be the perfect blend of GUI and SQL code for me to get the job done safely and efficiently.

i hope this helps others in a similar situation.

Solution 3

If you are trying to delete one of the duplicate rows in a table that has no unique identifier, you could try something like.

DELETE TOP(1) FROM theTable WHERE condition1, condition2,...

You should test it first with a SELECT statement before you apply the delete query.

Solution 4

I've also found if you a text or ntext column you will get his error. I converted that column to NVARCHAR(MAX) and haven't had any problems since.

View more solutions