mysql - How can I update the value of one field to the most often used value of another field? -

i have table similar following:

id     payee     category
001  costco      grocery
002  see's        candy
003  costco      repair
005  costco      grocery
006  costco
007  costco
008  see's

using mysql without aid of programming language, there query (nested or not) set category of 3 new rows used category payees?

for example, 1 of costco records (id 003) has repair category, whereas other 2 costco rows (id 001 , id 005) have grocery category. desired result new costco rows (id 006 , id 007) set grocery since that's used category payee.

sure.. change 'your_table' name of table

update your_table left join (select payee, category                            (select payee, category your_table category != '' , category not null group payee, category order count(*) desc) tbl2              group payee            ) tbl2 using (payee) set your_table.category = tbl2.category; 

this change costco categorized repair 'grocery' well.. if dont want this, add:

  your_table.category null or your_table.category = ''     

to end of query