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.
4 Answers
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
4 Comments
zerkms
Are you sure mysql allows joining the same table in
DELETE?
Zane Bien
@zerkms, I belive it's only when you try to reference the same table in a WHERE subquery that MySQL would throw an error. I just tried to use
DELETE FROM tbl WHERE id NOT IN (SELECT MIN(id) FROM tbl GROUP BY name) and it gave me "You can't specify target table 'tbl' for update in FROM clause:". MySQL Documentation says "Currently, you cannot delete from a table and select from the same table in a subquery." which is what I believe that was referring to (my WHERE NOT IN query).zerkms
yep, that's strange. Because subquery can be rewritten to
LEFT JOIN (not sure 100%, but I bet so)Zane Bien
@zerkms, indeed it is. I also tried it with a couple variations of correlated
NOT EXISTSs, which I know sometimes gets rewritten as LEFT JOINs... but still the same error every time.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)
1 Comment
dave
Sorry... What does it mean "like we do in excel"?
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;
Comments
- 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';
- 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
Comments
1 ABCnot6 ABC?