1

Is it possible to delete perfect duplicate records from mysql, without creating a temporary table in a single query????

INSERT INTO `test` (`fruit`, `price`) VALUES
('apple', 10),
('grape', 50),
('apple', 10),
('orange', 100),
('orange', 100),
('orange', 100),
('pinaple', 200),
('pinaple', 200),
('pinaple', 200),
('pinaple', 200);

and the result should be

"apple","10"
"orange","100"
"pinaple","200"
"grape","50"

in a single query

Description:

If there is any uniquely identifiable fields then remove the duplicates like this Remove duplicate rows in MySQL

If the there is only one particular item then can be able to remove the duplicates by using limit of delete query

DELETE FROM table_name WHERE column_name='value' LIMIT 1;

Multi line solution link2

Alex Mathew
  • 3,925
  • 5
  • 21
  • 25

1 Answers1

1

You could add a unique index to the table as follows:

ALTER TABLE yourTable ADD CONSTRAINT unique_fruits UNIQUE (fruit, price)

This should remove duplicates in a single statement without a temporary table. If you don't want the unique constraint moving forward, then you can remove it:

ALTER TABLE yourTable DROP INDEX unique_fruits
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360