CODEWITHSUNDEEP
mysqlsql
Fin Dav
Fin Dav

Reputation: 53

how to remove duplicate values from MySQL table

i am looking for the query, deletes the all duplicate values.

Example Table:

1 ABC
2 BBB
3 DAC
4 ABC
5 AAA
6 ABC

output required

1 ABC
2 BBB
3 DAC
5 AAA

thanks for your help, i Google it can't find exact solution.

Upvotes: 2

Views: 11102

Answers (4)

Mateen
Mateen

Reputation: 1671

  1. load distinct data in a file and delete or drop your table

SELECT DISTINCT col1,col2,col3,..coln FROM table name INTO OUTFILE 'yourFilePathWIthFileName'

eg: SELECT DISTINCT username,usernumber,usersalary,dateOFJoining,timeofJoining,datetimeOfJoining FROM asdf INTO OUTFILE 'C:/Users/ahmed.m/Desktop/modify.txt';

  1. create your table structure laod data back from file

LOAD DATA INFILE 'yourFilePathWIthFileName' INTO TABLE tableName eg: LOAD DATA INFILE 'C:/Users/ahmed.m/Desktop/modify.txt' INTO TABLE asdf

Upvotes: 0

Zane Bien
Zane Bien

Reputation: 23125

If you want to do an actual DELETE operation of the duplicate values (while retaining the values having the lowest id), you can do it with the multiple table DELETE syntax:

DELETE a FROM tbl a
LEFT JOIN
(
    SELECT MIN(id) AS id, name
    FROM tbl
    GROUP BY name
) b ON a.id = b.id AND a.name = b.name
WHERE b.id IS NULL

See a demonstration of the DELETE operation

Upvotes: 6

dave
dave

Reputation: 2269

Assuming Tab is the name of your table containing duplicates, create a temporary table Tab_TMP with the same structure of Tab.

-- assuming `Tab` has same schema
CREATE TABLE Tab_TMP (
  id INT(2) PRIMARY KEY,
  name VARCHAR(8)
);

Fill Table_TMP with all Table entries.

INSERT INTO Tab_TMP(id, name) SELECT id, name FROM Tab;

Delete entries from Table.

DELETE FROM Tab;

Insert in Table entries from Table_TMP using SELECT DISTINCT.

INSERT INTO Tab(name) SELECT DISTINCT name FROM Tab_TMP;

Upvotes: 0

user1166147
user1166147

Reputation: 1610

See @fvu answer here : https://stackoverflow.com/a/11249235/1166147

You can create a unique index that will remove duplicates and prevent future dupes all at once (MySQL 5.1 or higher):

ALTER IGNORE TABLE 'YOURTABLE' 
ADD UNIQUE INDEX somefancynamefortheindex (Col1ID, Col2)

Upvotes: 2

Related Questions