how to update a table row value using a row value from another table in Apex Oracle SQL?

Question

I have a bulk stock table with a quantity variable. I also have a quantity variable for a table called purchase order. When a user makes a purchase order I want the quantity they have inserted in the purchase order table to be deleted in the Bulk stock table. I am doing this using an SQL oracle software called Apex. I am struggling with SQL syntax i believe:

Error: ORA-01747: invalid user.table.column, table.column, or column specification

this is my code

UPDATE Bulk_stock
   SET Bulk_stock.bulk_quantity = Bulk_stock.bulk_quantity - purchase_order.order_quantity,

 WHERE EXISTS
      (SELECT 1
         FROM purchase_order
        WHERE Bulk_stock.stock_id = purchase_order.stock_id);

Could you pass in the value of the current purchase order id into the query as a bind variable? How does the rest of your code look like? — Mick Mnemonic, Nov 16 '16 at 00:18
The insert is done automatically using that oracle apex form. This is the code i want to put as a process on the page AFTER the insert is done according to the image above. there is no extra code — kuhle, Nov 16 '16 at 06:58
Are you allowed to create a `TRIGGER` in the database for encapsulating this business logic? — Mick Mnemonic, Nov 16 '16 at 07:58
yes I am allowed. I tried creating a trigger but apex always cries about syntax. If you could help with a Begin-End; correct syntax for this kind of update. — kuhle, Nov 16 '16 at 09:41
This one worked for me http://stackoverflow.com/questions/40631582/how-to-trigger-an-update-on-a-table-row-value-using-a-row-value-from-another-tab/40632289#40632289 — kuhle, Nov 19 '16 at 12:01

randominstanceOfLivingThing · Answer 1 · 2016-11-16T00:07:47.643

0

There is an extra comma after purchase_order.order_quantity. Besides in your update statement you refer to purchase_order.order_quantity which is not correct since the update is happening on Bulk_stock table.

Can you try this one:

UPDATE Bulk_stock
   SET Bulk_stock.bulk_quantity = Bulk_stock.bulk_quantity - nvl( SELECT purchase_order.order_quantity
         FROM purchase_order
        WHERE purchase_order.stock_id = Bulk_stock.stock_id WHERE ROWNUM=1), 0) WHERE EXISTS
  (SELECT 1
     FROM purchase_order
    WHERE Bulk_stock.stock_id = purchase_order.stock_id);

edited Nov 16 '16 at 00:07

answered Nov 15 '16 at 22:57

randominstanceOfLivingThing

16,873
13
49
72

This would always update all rows in `Bulk_stock`, right? – Mick Mnemonic Nov 15 '16 at 23:54
Yes. But after update, the result will be that the bulk_quantity will remain unchanged if no value else will change to the value to reflect quantity that is ordered. – randominstanceOfLivingThing Nov 16 '16 at 00:04
Yea, my point was that this could be an expensive operation if the table has millions of rows. – Mick Mnemonic Nov 16 '16 at 00:06
1

Modified the query to prevent update if no orders exist for the item in stock. – randominstanceOfLivingThing Nov 16 '16 at 00:07
This still might not be what the OP is after, because it would keep reducing the quantity of orders that had already been processed earlier, during each update. – Mick Mnemonic Nov 16 '16 at 00:18
Isn't `SET Bulk_stock.bulk_quantity = Bulk_stock.bulk_quantity - purchase_order.order_quantity` doing what I was doing? There was no order processing information in the query @kuhle specified. – randominstanceOfLivingThing Nov 16 '16 at 00:23

how to update a table row value using a row value from another table in Apex Oracle SQL?

1 Answers1