update with join sql | error: ORA-00933: SQL command not properly ended

Question

i have to lower the price with 2% for each item where the capacity is more than 200 pcs using two tables where the id's have to be the same and i can not pass this error: ORA-00933: SQL command not properly ended

'Catalog' is a table and 'Order' is the second table

update Catalog
set price = (price - price*0.02) from Catalog inner join Order on 
(Catalog.idf = Order.idf and Catalog.idp = Order.idp)
where quantity > 200;

what could be the bug here? thanks!

Answer 1

Oracle does not support a FROM clause in an UPDATE.

Hmmm. If I speculate that quantity is in order, then you can use:

update Catalog
    set price = 0.98 * price
    where (select o.quantity
           from Orders o 
           where Catalog.idf = Order.idf and Catalog.idp = Order.idp
          ) > 200;

I am a bit cautious about this. This means that there is a 1-1 relationship between orders and catalog. If there can be more than one order for a catalog entry, then you probably need aggregation. However, this is the logic you seem to be trying to implement in the question.

Answer 2

You can also use merge statement as follows:

Merge into Catalog c
Using (select * from orders) O
On (c.idf = O.idf and C.idp = O.idp
    And o.quantity > 200)
When matched then
Update set c.price = 0.98 * price;