How can I do an UPDATE statement with JOIN in Appex to calculate to attribute

Question

I want to calculate the sum of value + service fees from a table booking and bill



and the table of bill
CREATE TABLE BILL (BILL_NUM ,SERVICE_FEES,TOTAL_AMOUNT 
BILL_DATE ,BOOKING_NUM number REFERENCES BOOKING(BOOKING_NUM)
)

my booking table contain attribute of (returndate,bookingdate,references customer(cusnum), references location(locnum), references insurance (insurancenum), bookingnum, references cars(car-regestrtion-num), amount )

UPDATE BILL AS A
JOIN BOOKING AS B ON A.Booking_num = B.Booking_num 
SET A.Total_Amount = B.amount + A.Service_Fees;

please help me to fix the error

Answer 1

Something like this?

update bill a set
  a.total_amount = (select b.amount + a.service_fees
                    from booking b
                    where b.booking_num = a.booking_num
                   )
where exists (select null
              from booking c
              where c.booking_num = a.booking_num
             );

---

Or, use merge (which is simpler):

merge into bill a
  using booking b
  on (a.booking_num = b.booking_num)
when matched then update set
  a.total_amount = b.amount + a.service_fees;