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?
This question is related to
mysql
delete-row
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;
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...
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)
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.
Add a limit
to the delete query
delete from orders
where id_users = 1 and id_product = 2
limit 1
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.
Source: Stackoverflow.com