I have a table name as bursary,
there is duplicate entries in the username, how can I delete the duplicate entries.?
here is my bursary table,
UserID UserName
1 ca11074
2 ca11074
3 cb56456
4 cb56456
I need to delete duplicate entries.
here I tried but unfortunately not working.
$sql =
"DELETE FROM bursary
WHERE UserName IN (SELECT UserName FROM bursary (COUNT(*) > 1)";
$result=mysql_query($sql);
Try this :
$sql = "ALTER IGNORE TABLE `bursary` ADD UNIQUE INDEX index_name (`UserName`);";
$result = mysql_query($sql);
You can use INNER JOIN.
DELETE a
FROM YOUR_TABLE AS a
INNER JOIN YOUR_TABLE AS b ON a.UserName = b.UserName
WHERE a.UserID < b.UserID;
Or you can create another TEMP_TABLE, select distinct values from YOUR_TABLE to TEMP_TABLE, after it truncate YOUR_TABLE and copy TEMP_TABLE data to YOUR_TABLE and delete the TEMP_TABLE.
This will delete duplicates. It'll keep the row with the lowest ID.
DELETE FROM bursary a
WHERE exists (select 1 from bursary b
where a.UserName = b.UserName and
a.id > b.id)
You can apply unique key to your Username column will delete duplicate entries.
You can try below query:
Delete bs from bursary bs,bursary bs2
where bs.UserID > bs2.UserID and bs.UserName = bs2.UserName;
Using above query you can delete duplicate username entries.
delete from bursary b1
where rowid > (SELECT min(rowid) FROM bursary b2 group by
b2.UserID,b2.UserName);
please use add unique with ignore to delete all duplicate value
ALTER IGNORE TABLE `my_table`
ADD UNIQUE (`Username`);
After that you can remove unique if you want.
