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?

Answer 1

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.