How can I do an UPDATE statement with JOIN in Appex

Question

i have try to add this query but the system does not accepted. how i can fix it please.

i want update the whole part in booking using join

my tables are i have created table called res as

CREATE TABLE res
AS
   (SELECT booking.car_regestration_num,
           rent.cost_per_day,
           (booking_date - return_day) AS amount
      FROM rent
           JOIN booking
              ON booking.car_regestration_num = rent.car_regestration_num);

and booking table as

CREATE TABLE booking
(
   return_day             DATE NOT NULL,
   booking_date           DATE NOT NULL,
   cus_num                NUMBER REFERENCES customer (cus_num),
   loc_num                NUMBER REFERENCES c_location (loc_num),
   ins_num                NUMBER REFERENCES insurance (ins_num),
   booking_num            NUMBER PRIMARY KEY,
   car_regestration_num   NUMBER REFERENCES cars (car_regestration_num),
   amount                 BINARY_FLOAT
);

i have tried what i show you. i accept solve the problem as much as possible

the purpose of this Q is to add the amount in booking table with values

UPDATE BOOKING JOIN res  
ON BOOKING.CAR_REGESTRATION_NUM = res.CAR_REGESTRATION_NUM
SET BOOKING.amount = res.amount;

Post table definitions as text, not images. You've tagged this question for both MySQL and Oracle, two different databases with different SQL dialects. Is there some reason that you specifically need to use a `join` here rather than just a correlated update? — Justin Cave, Feb 21 '23 at 01:16

score 0 · Answer 1 · answered Feb 21 '23 at 08:15

0

merge seems to be simple enough:

MERGE INTO booking b
     USING res r
        ON (r.car_regestration_num = b.car_regestration_num)
WHEN MATCHED
THEN
   UPDATE SET b.amount = r.amount;

answered Feb 21 '23 at 08:15

Littlefoot

131,892
15
35
57

score 0 · Answer 2 · answered Feb 21 '23 at 08:31

Oracle does not support using JOINs in an UPDATE statement. You can use correlated sub-queries instead.

If you want to update all the rows:

UPDATE BOOKING b
SET amount = ( SELECT amount
               FROM   res r
               WHERE  b.CAR_REGESTRATION_NUM = r.CAR_REGESTRATION_NUM );

If you want to update only the matching rows:

UPDATE BOOKING b
SET   amount = ( SELECT amount
                 FROM   res r
                 WHERE  b.CAR_REGESTRATION_NUM = r.CAR_REGESTRATION_NUM )
WHERE EXISTS(
                 SELECT 1
                 FROM   res r
                 WHERE  b.CAR_REGESTRATION_NUM = r.CAR_REGESTRATION_NUM
            );

How can I do an UPDATE statement with JOIN in Appex

2 Answers2

Related