CODEWITHSUNDEEP
sqloracle-databasesql-updateinner-join
Gabriel Knies
Gabriel Knies

Reputation: 359

ORACLE SQL UPDATE with INNER JOIN isn't working

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

Upvotes: 0

Views: 1166

Answers (3)

Atif
Atif

Reputation: 2210

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

Upvotes: 0

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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

Upvotes: 2

Related Questions