如何从mysql表中删除具有相同列值的行?

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

我的MySQL查询有问题。我的表格有4列，看起来是这样的:

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.

id_users和id_product是来自不同表的外键。

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?

但是它会删除它们(因为它们是重复的)。有什么解决这个问题的建议吗?

6 个解决方案

#1

177

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:

所有表都应该有一个主键(由一个或多个列组成)，重复的行在关系数据库中没有意义。您可以使用limit限制删除行数:

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:

我将给出其他方式:

By creating Unique index
通过创建唯一索引

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

我认为id_users和id_product在您的示例中应该是唯一的。

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;

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...

您必须为每一行添加一个自动递增的id，之后您可以通过它的id来删除该行。

#1

177