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

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

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

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

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

Examples related to mysql

Examples related to delete-row