To delete rows in a MySQL table, use the DELETE FROM statement: DELETE FROM products WHERE product_id=1; The WHERE clause is optional, but you'll usually want it, unless you really want to delete every row from the table.
There are a few ways to delete multiple rows in a table. If you wanted to delete a number of rows within a range, you can use the AND operator with the BETWEEN operator. DELETE FROM table_name WHERE column_name BETWEEN value 1 AND value 2; Another way to delete multiple rows is to use the IN operator.
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.
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:
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;
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With