SQL update values based on join

Question

I'm trying to update values of one table based on criteria of second table. But something is wrong. Can you advise what I'm a doing wrong?

UPDATE food_serve
SET food_serve_cost = food_serve_cost*1.15
FROM food_serve JOIN fooditem  
ON fooditem.food_item_no = food_serve.food_item_no
WHERE food_type = 'M' ;

Using oracle sql. Error report -

SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:
*Action:

Answer 1

Hello I think you miss something in food_serve_cost You didnt use any alias

UPDATE ( SELECT food_serve.food_serve_cost , 
fooditem.food_serve_cost
FROM food_serve
INNER JOIN fooditem ON fooditem.food_item_no =   
food_serve.food_item_no
WHERE fooditem.food_type = 'M')
SET food_serve.food_serve_cost=fooditem.food_serve_cost*1.15

Or You can use merge

MERGE into food_serve
USING fooditem
ON (fooditem.food_item_no = food_serve.food_item_no)
when matched then update SET     
food_serve.food_serve_cost=fooditem.food_serve_cost*1.15
WHERE fooditem.food_type = 'M'

Answer 2

Seems you have ambiguous columns try add the table name for example:

UPDATE food_serve
SET food_serve.food_serve_cost = fooditem.food_serve_cost*1.15
FROM food_serve JOIN fooditem  
ON fooditem.food_item_no = food_serve.food_item_no
WHERE fooditem.food_type = 'M' ;

Answer 3

You can make use of Merge Operator better in this situation

               select * into #Target from fooditem  WHERE food_type ='M' 

                     MERGE food_serve AS T
                     USING #Target AS S 
                     ON S.food_item_no = T.food_item_no 
                     WHEN MATCHED THEN
                     UPDATE SET T.food_serve_cost = T.food_serve_cost*1.15