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

Question

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

User · Answer

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.

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;

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Add a limit to the delete query  delete from orders  where id users   1 and id product   2 limit 1

User · Answer

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

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

Examples related to mysql

Examples related to delete-row