php - Removing Duplicated Entries: Delete a entry that has 2 the same column -

i have table called bag:

+--------+----------+---------+----------+ | bag_id | chara_id | item_id | item_qty | +--------+----------+---------+----------+ |      1 |        1 |       2 |       22 | |      2 |        1 |       1 |       55 | |      3 |        3 |       1 |        2 | |      6 |        3 |       4 |        2 | |      7 |        4 |       4 |        2 | |      8 |        5 |       4 |        2 | |      9 |        6 |       4 |        2 | |     10 |        1 |       5 |        1 | |     11 |        1 |       2 |        1 | |     12 |        1 |       2 |        1 | |     13 |        1 |       2 |        1 | |     14 |        1 |       8 |        1 | |     15 |        1 |       6 |        1 | |     16 |        1 |       8 |        1 | |     17 |        1 |       6 |        1 | +--------+----------+---------+----------+ 

the relationship goes 1 chara = many item dont want 1 chara = many duplicated item.

how can make query delete's duplicated values? chara_id: 1 has 3 duplicated item_id: 2 want delete other 2.

you can join table bag subquery gets minimum bag_id every combination of chara_id , item_id. records have null values on fields on subquery records deleted.

delete     bag         left join         (             select  chara_id, item_id, min(bag_id) min_id                bag             group   chara_id, item_id         ) b on  a.bag_id = b.min_id ,                 a.chara_id = b.chara_id ,                 a.item_id = b.item_id   b.min_id null 

• sqlfiddle demo