update item_batch set cost = (select sum(bom_items.quantity*its.cost) from items inner join boms on items.id = boms.item_id and boms.active = 1 inner join bom_items on boms.id = bom_items.bom_id inner join ( select item_batch.item_id, max(item_batch.id) as id from item_batch group by item_batch.item_id ) as it on it.item_id = bom_items.item_id inner join item_batch as its ON its.id = it.id where items.bom = 1 and items.id = item_batch.item_id group by items.name, items.number) where exists(select 1 from items inner join boms on boms.item_id = items.id inner join bom_items on boms.id = bom_items.bom_id where items.id = item_batch.item_id and items.bom = 1);