How to delete a certain row from mysql table with

问题:

I have a problem with my queries in MySQL. My table has 4 columns and it looks something like this:

id_users    id_product    quantity    date
 1              2              1       2013
 1              2              1       2013
 2              2              1       2013
 1              3              1       2013

id_users and id_product are foreign keys from different tables.

What I want is to delete just one row:

1     2     1    2013

Which appears twice, so I just want to delete it.

I've tried this query:

delete from orders where id_users = 1 and id_product = 2

But it will delete both of them (since they are duplicated). Any hints on solving this problem?

回答1:

Add a limit to the delete query

delete from orders 
where id_users = 1 and id_product = 2
limit 1

回答2:

All tables should have a primary key (consisting of a single or multiple columns), duplicate rows doesn't make sense in a relational database. You can limit the number of delete rows using LIMIT though:

DELETE FROM orders WHERE id_users = 1 AND id_product = 2 LIMIT 1

But that just solves your current issue, you should definitely work on the bigger issue by defining primary keys.

回答3:

You need to specify the number of rows which should be deleted. In your case (and I assume that you only want to keep one) this can be done like this:

DELETE FROM your_table WHERE id_users=1 AND id_product=2
LIMIT (SELECT COUNT(*)-1 FROM your_table WHERE id_users=1 AND id_product=2)

回答4:

Best way to design table is add one temporary row as auto increment and keep as primary key. So we can avoid such above issues.

回答5:

There are already answers for Deleting row by LIMIT. Ideally you should have primary key in your table. But if there is not.

I will give other ways:

1. By creating Unique index

I see id_users and id_product should be unique in your example.

ALTER IGNORE TABLE orders ADD UNIQUE INDEX unique_columns_index (id_users, id_product)

These will delete duplicate rows with same data.

But if you still get an error, even if you use IGNORE clause, try this:

ALTER TABLE orders ENGINE MyISAM;
ALTER IGNORE TABLE orders ADD UNIQUE INDEX unique_columns_index (id_users, id_product)
ALTER TABLE orders ENGINE InnoDB; 

2. By creating table again

If there are multiple rows who have duplicate values, then you can also recreate table

RENAME TABLE `orders` TO `orders2`;

CREATE TABLE `orders` 
SELECT * FROM `orders2` GROUP BY id_users, id_product;

回答6:

You must add an id that auto-increment for each row, after that you can delet the row by its id. so your table will have an unique id for each row and the id_user, id_product ecc...