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

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?

Add a limit to the delete query

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

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.

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)

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.