How to delete a certain row from mysql table with same column values?

153
votes

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?

6
mysqldelete-row

6 Answers

208
votes

Add a limit to the delete query

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

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.

20
votes

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)
8
votes

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
votes

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;
  1. 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;
1
votes

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