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

Oracle Update does not work that way. You cannot qualify tables with the `as` expression, and inner join cannot be used either — Roberto Hernandez, Sep 09 '20 at 08:22
Does this answer your question? [Update statement with inner join on Oracle](https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle) — William Robertson, Sep 09 '20 at 10:41

score 2 · Accepted Answer · answered Sep 09 '20 at 08:22

2

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);

answered Sep 09 '20 at 08:22

Tim Biegeleisen

502,043
27
286
360

1

@GabrielKnies: this solution is likely to be more efficient than the accepted answer using the `merge` statement, which has an unnecessary addition join in the `using` clause. – GMB Sep 10 '20 at 00:25
@GMB Do you review old answers on a regular basis? – Tim Biegeleisen Sep 10 '20 at 00:45

score 0 · Answer 2 · answered Sep 09 '20 at 08:29

As an alternative to the update command, you can use merge instead

merge into orders target 
using 
( SELECT t2.order_id , SUM(t2.total_price) AS p_total 
      FROM order_lines t2 inner join orders t1
      ON  t1.order_id = t2.order_id 
      GROUP BY t2.order_id ) source 
    on ( target.order_id = source.order_id ) 
when matched then 
update set target.total_price_cache = source.p_total

score 0 · Answer 3 · answered Sep 09 '20 at 08:49

You can convert the MYSQL update with join into key preversed view in Oracle

UPDATE (SELECT t1.total_price_cache AS total_price_cache,
                t1.order_id,
                t2.order_id
                orders AS t1 ,
                t2.p_total
                FROM 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

ORACLE SQL UPDATE with INNER JOIN isn't working

3 Answers3