Deleting Duplicate Row Causes All Rows To Be Deleted

[Marty Martin]

Let's say I have 2 rows like-

Foo Bar | 12345

Foo Bar | 12345

Now, in SQLyog I put a check next to the first one and click the Delete Row icon. Instead of deleting just the one row, it is deleting both rows.

Is there a way around this? I reckon it's because they're exactly the same?

The easy thing would be to just modify one of the rows, but when you're talking about 100s of rows that becomes a pain.

Thanks for any advice/help.

[peterlaursen]

There is not much idea in having identical data/duplicate rows in a database. but at least you should add a Primary Key to the table.  Please read:

http://webyog.com/faq/content/28/70/en/i-only-changed-one-row-of-data-but-sqlyog-wants-to-update-more-rows-in-the-database-why.html

You can neither DELETE nor UPDATE change) a single row.  This is because of the nature of SQL. The WHERE-clause identifying one row will identify them all if there is no Primary Key. The Primary Key will enusre that each row is uniquely identifued. But what is your setting here?

[attachment=1325:multi.jpg]

[DonQuichote]

There is a trick: Issue an ALTER IGNORE TABLE command with a uniqueness constraint. It will delete any superfluous rows (if permitted by any other foreign key constraints off course). After that, you can optionally delete that constraint. But it is good to have an identity column in every table (autonumber primary key).

[Marty Martin]

Okay, thanks to both of you, this definitely answers my question. Time to insert a primary key column 🙂

[Marty Martin]

'peterlaursen' wrote on '23:

But what is your setting here?

[attachment=1325:multi.jpg]

I have that unchecked because it's a real pain when I delete 50 rows to have it prompt me “Are you sure” every 5/random number of rows.

[peterlaursen]

Well .. then it is all explained.  The WHERE clause in the DELETE statement will match all rows. I am not able to understand why you want to have duplicate rows.  But you may copy the DELETE statement from HISTORY tab and add “LIMIT 1” at the end.  But there still is no way to control *which* of the rows is deleted.  The MySQL server will delete the first one it finds.  Even the server cannot distinguish them. You should have a Primary Key in you tables.

[Marty Martin]

'peterlaursen' wrote on '27:

Well .. then it is all explained.  The WHERE clause in the DELETE statement will match all rows. I am not able to understand why you want to have duplicate rows.  But you may copy the DELETE statement from HISTORY tab and add “LIMIT 1” at the end.  But there still is no way to control *which* of the rows is deleted.  The MySQL server will delete the first one it finds.  Even the server cannot distinguish them. You should have a Primary Key in you tables.

Agreed. I don't want duplicate rows; the problem was in importing data from spreadsheets, there would often be duplicate rows of data, but when I would try to delete all but one of the duplicates, it deleted them all. I added the PK and all is fine now. Thanks!

[Author]

Posts