Oracle Transaction Update Amount From One Table to Another

Question

I'm creating a database that contains four different tables:

CUSTOMER (PK CUST_ID, CUST_NAME, CUST_ADDRESS)

INVOICE (PK INVOICE_ID, FK CUST_ID, INVOICE_AMOUNT)

PRODUCT (PK PRODUCT_ID, PRODUCT_NAME, PRODUCT_COST)

INVOICE_ITEM (PK FK INVOICE_ID, PK FK PRODUCT_ID, 
INVOICE_ITEM_QUANTITY, INVOICE_ITEM_PRICE)

I need to create a transaction that 1). inserts data into a new CUSTOMER, 2). inserts data into a new INVOICE (invoice amount set to 0), 3). inserts data of two new INVOICE_ITEM (one row has INVOICE_ITEM_QUANTITY set to 2 and INVOICE_ITEM_PRICE to 5.00, the other to 1 and 10.00).

I can do all of this without any issue using insert commands, but the next step is tripping me up:

'Update the INVOICE_AMOUNT to add the line items added in step 3 to the INVOICE_ITEM_PRICE field. You must do this with a query that would work regardless if there were existing INVOICE_ITEM rows for this INVOICE. Your query cannot hard code the amount.'

I can't figure out how I would go about doing this. I've been researching solutions, but haven't found anything that describes this problem, only ones that work if the column name is the same.

I could just insert the values directly into the INVOICE_AMOUNT, but then it would be considered hard coding the answer, correct? So I've been trying to do something along the lines of

UPDATE INVOICE
SET INVOICE_ITEM.INVOICE_ITEM_PRICE = INVOICE.INVOICE_AMOUNT
WHERE INVOICE.INVOICE_ID = INVOICE_ITEM.INVOICE_ID

but I can't get any variation of this code to work. I'm sure there is some really simple answer that will make me feel stupid, but if anyone can help, I'd greatly appreciate it.

To me the Invoice_Amount should be the `sum(invoice_Item.Invoice_Item_Price*Invoice_Item_Quantity)` so you need to get that sum ensure it's not null (or at least make it 0 if its null) — xQbert, Apr 13 '17 at 15:00
I am aware of this as well, but in my assignment it is noted: It is questionable on whether you should have an an INVOICE_AMOUNT field given you can always query the INVOICE_ITEM table and sum up the INVOICE_ITEM_QTY * INVOICE_ITEM_PRICE for each invoice item. However, for this assignment we are assuming we want to do this. — CBevs, Apr 13 '17 at 15:09
Similar question: http://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle — xQbert, Apr 13 '17 at 15:13

score 0 · Accepted Answer · answered Apr 13 '17 at 15:22

This might be a bit clunky but given what you have and no indication that PL/SQL is used then;

UPDATE invoice i
SET    i.invoice_amount = (SELECT SUM(ii.invoice_item_quantity * ii.invoice_item_price)
                           FROM   invoice_item ii
                           WHERE  ii.fk_invoice_id = 1.invoice_id)
WHERE  i.invoice_id = (SELECT MAX(invoice_id)
                       FROM   invoice
                       WHERE  fk_cust_id = (SELECT MAX(cust_id)
                                            FROM   customer) )

So . . .set the invoice amount to the sum of the qty * price for invoice items for the invoice. Just update the invoice records that have the latest invoice ID and that latest invoice is for the latest customer. This assumes you are creating the PK values using a sequence (the invoice SHOULD be taking the latest invoice number and adding 1 to it). The others could be simple Oracle sequences. Sorry for the clunkiness but its a virtual Friday here and I'm about to go home.

You are a legend among men. Thank you for giving me your time on your virtual Friday! — CBevs, Apr 13 '17 at 15:31

score 0 · Answer 2 · answered Apr 13 '17 at 15:51

You could just make a trigger that will be update invoice_ammount. Something like this:

CREATE OR REPLACE TRIGGER tg_invoice_item_amt 
  AFTER 
    DELETE OR 
    INSERT OR 
    UPDATE OF invoice_item_quantity, invoice_item_price
  ON invoice_item
  FOR EACH ROW
DECLARE
  l_change_amt NUMBER;
BEGIN
  CASE
    WHEN INSERTING THEN
      l_change_amt := COALESCE(:NEW.invoice_item_quantity * :NEW.invoice_item_price, 0);
    WHEN UPDATING THEN
      l_change_amt := COALESCE(:NEW.invoice_item_quantity * :NEW.invoice_item_price, 0) 
                      - COALESCE(:OLD.invoice_item_quantity * :OLD.invoice_item_price, 0);
    WHEN DELETING THEN
      l_change_amt := 0 - COALESCE(:OLD.invoice_item_quantity * :OLD.invoice_item_price, 0);
  END CASE;
  IF l_change_amt != 0 THEN
    UPDATE invoice SET invoice_amount = invoice_amount + l_change_amt;
END;
/

Oracle Transaction Update Amount From One Table to Another

2 Answers2