![]() |
how can i write this query - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5) +--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12) +--- Thread: how can i write this query (/showthread.php?tid=105) |
how can i write this query - rajneeshgobin - 11-04-2014 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. RE: how can i write this query - Jeroen - 11-05-2014 What you also can do to make it easier for you: $query = "your query here."; $this->db->query($query); RE: how can i write this query - Rufnex - 11-05-2014 for one machting filed you can us this statement Code: delete r1 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/2594829/finding-duplicate-values-in-a-sql-table |