mysql - sql update statement for two tables -

i using mysql 5.5 , have 2 tables, inventory_items , menu_items. both have bin_number string , location_id integer.

menu_items invetory_item_id | location_id | bin_number   inventory_items id | location_id | bin_number 

i'd update menu_items inventory_item_id id of inventory_items table equal on bin_number , location_id. go through each this:

update menu_items set inventory_item_id=(select id inventory_items  bin_number='7060'  , location_id=37) bin_number='7060'  , location_id=37; 

is there way update menu_items bin_number , location_id same between menu_items , inventory_items?

thx

you can use join in update:

 update menu_items mi       join inventory_items ii on mi.bin_number=ii.bin_number           , mi.location_id=ii.location_id  set mi.inventory_item_id = ii.id