Last updated on January 17, 2018
This post explains, how to prevent the duplicate occurrence in the table and how to deal with already existed duplicate records.
we can private duplicate record occurrence in the table with the use of primary key and Unique Key Index on appropriate filed.
Displaying Different Values(only once):
The DISTINCT keyword is used for displaying different values from the duplicate values. instead of DISTINCT we can use GROUP BY, it selects only unique Combination
SELECT DISTINCT name FROM table; // this will display only different values SELECT name FROM TABLE GROUP BY name; // this will display only different values
Counting Duplicate Occurrences in the Table
SELECT name,count(name) c from table group by label having c > 1;
Removing duplicate entries FROM the table
ALTER IGNORE TABLE table ADD UNIQUE KEY index_1(name);
We can also remove duplicate entries without altering table in the following way, FIRST create a temp table with unique id values, then run the delete command where ids are not on the temp table.
DELETE FROM tbl_menu WHERE id NOT IN (SELECT id FROM (SELECT id, label FROM tbl_menu GROUP BY label ORDER BY id) AS temp);