How do i use UPDATE and JOIN in SQL oracle?

Question

i have 2 tables fooditem and food_serve .i can view the data i want to change with

SELECT food_serve.food_serve_cost
FROM fooditem INNER JOIN food_serve
ON fooditem.food_item_no= food_serve.food_item_no and fooditem.food_type = 'M'and food_serve.food_serve_size = 'ST';

But im trying to update the values like this however its throwing errors at me

update q
set q.food_serve_cost = q.food_serve_cost *1.15
from food_serve q
inner join fooditem a
on q.food_item_no = a.food_item_no
where a.food_type = 'M' AND q.FOOD_SERVE_SIZE = 'ST';

after research i found that from cant be used in update but i cant find a fix for this Any help please? im using oracle sql developer

This might help https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle — Anshuman Mitra, Oct 07 '17 at 05:09
Even more duplicates here: https://stackoverflow.com/search?q=%5Boracle%5D+update+with+join — , Oct 07 '17 at 07:03

navdeep singh · Answer 1 · 2017-10-07T06:14:14.960

0

update food_serve q
inner join fooditem a
on q.food_item_no = a.food_item_no
set q.food_serve_cost = q.food_serve_cost *1.15
where a.food_type = 'M' AND q.FOOD_SERVE_SIZE = 'ST';

edited Oct 07 '17 at 06:14

answered Oct 07 '17 at 05:52

navdeep singh

1
3

score 0 · Answer 2 · answered Oct 07 '17 at 05:52

UPDATE ... FROM ... syntax is coming from PostgreSQL Update.
This is proprietary syntax and doesn't work on other RDBMS systems.

Use this query

update q
set q.food_serve_cost = q.food_serve_cost *1.15
WHERE EXISTS (
  SELECT null FROM fooditem a
  WHERE q.food_item_no = a.food_item_no
    AND a.food_type = 'M'
)
AND q.FOOD_SERVE_SIZE = 'ST'

or it's variation:

update q
set q.food_serve_cost = q.food_serve_cost *1.15
WHERE food_item_no IN (
        SELECT food_item_no  FROM fooditem 
        WHERE food_type = 'M'
     )
 AND FOOD_SERVE_SIZE = 'ST'

How do i use UPDATE and JOIN in SQL oracle?

2 Answers2