ORACLE SQL UPDATE with INNER JOIN isn't working

Question

I'm currently learning SQL, and I'm using ORACLE DATABASE. I've done the exercises of this website : https://sql.sh/exercices-sql/commandes and I'm doing the exact same things but for Oracle Database this time.

My problem is the following :

I have these two tables :

CREATE TABLE orders(
  order_id INT GENERATED BY DEFAULT AS IDENTITY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  reference VARCHAR(255) NOT NULL,
  total_price_cache DECIMAL(10, 3) NOT NULL,
  PRIMARY KEY(order_id)
);

CREATE TABLE order_lines(
  order_line_id INT GENERATED BY DEFAULT AS IDENTITY,
  order_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10, 3) NOT NULL,
  total_price DECIMAL(10, 3) NOT NULL,
  PRIMARY KEY(order_line_id)
);

And I'm trying to do the following :

UPDATE orders AS t1 
INNER JOIN 
    ( SELECT order_id, SUM(order_lines.total_price) AS p_total 
      FROM order_lines 
      GROUP BY order_id ) t2 
          ON  t1.order_id = t2.order_id 
SET t1.total_price_cache = t2.p_total

I works for MySQL but doesn't for Oracle Database. I keep getting the error ORA-00971 saying that the keyword SET is absent.

Can someone explain me how to do this for Oracle database.

Thanks

Answer 1

Oracle does not support this MySQL-style update join syntax. But, you may use a correlated subquery instead:

UPDATE orders AS o
SET total_price_cache = (SELECT SUM(total_price) FROM order_lines ol
                         WHERE ol.order_id = o.order_id);