CODEWITHSUNDEEP
phpmysql
satish
satish

Reputation: 41

Delete duplicate entries mysql

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);

Upvotes: 0

Views: 185

Answers (7)

Sree Gottumukkala
Sree Gottumukkala

Reputation: 21

delete from bursary b1 
where rowid > (SELECT min(rowid) FROM bursary b2 group by 
               b2.UserID,b2.UserName);

Upvotes: 0

Ashish Bhavsar
Ashish Bhavsar

Reputation: 236

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.

Upvotes: 0

Hayk Manasyan
Hayk Manasyan

Reputation: 506

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.

Upvotes: 1

rohitr
rohitr

Reputation: 371

You can apply unique key to your Username column will delete duplicate entries.

Upvotes: 0

Hans Kilian
Hans Kilian

Reputation: 25179

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)

Upvotes: 0

Hamed Kamrava
Hamed Kamrava

Reputation: 12847

Try this :

$sql = "ALTER IGNORE TABLE `bursary` ADD UNIQUE INDEX index_name (`UserName`);";
$result = mysql_query($sql);

Upvotes: 1

hoangvu68
hoangvu68

Reputation: 865

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.

Upvotes: -1

Related Questions