how can i write this query

i need a query which will delete duplicate entries from my database.

the query below tells me which rows of my table has duplicates based on the selected parameters in this case role id and perm id

select roleID, permID, count(*) as NumDuplicates
from `role_perms`
group by roleID, permID
having NumDuplicates > 1

from the previous forum a member converted this to

$this->db
->select('roleID, permID')
->select('count(*) as NumDuplicates', FALSE)
->group_by(array('roleID', 'permID'))
->having('NumDuplicates > 1')
->get('role_perms')
->result();

i wish to extend this so that it delete the duplicate values and keep only one in the table. if there is for example 1 original entry and 2 more duplicates, then it deletes teh 2 duplicate leaving the 1 entry.

What you also can do to make it easier for you:

$query = "your query here.";
$this->db->query($query);

for one machting filed you can us this statement

Code:

delete r1
    from role_perms r1, role_perms r2
where
    r1.duplicate= r2.duplicate
and
    r1.id > r2.id

$this->db->query('delete r1 from role_perms r1, role_perms r2 where r1.duplicate = r2.duplicate and r1.id > r2.id');

It deletes all duplicate rows based on the field 'duplicate' and leaving the row with the lowest id. Test it before you use it in production

If you need more matches look at this usefull post:

http://stackoverflow.com/questions/25948...-sql-table