Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Unanswered: Update a running total field from one table to another

    I have a table with Vendor invoices that has invoice number,po number and total invoice amount. I need to update another table that keeps a running total of the amount spent on the po from the vendor invoice table. This is the statement I tried (along with many variations) and cannot seem to get it to work:
    SQL> UPDATE ap_inv a SET tot_1= tot_1 + (Select v.inv_tot_amount from vn_inv v WHERE a.inv_num=v.inv_num AND a.po_num=v.po_num);

    UPDATE ap_inv a SET tot_1= tot_1 + (Select v.inv_tot_amount from vn_inv v WHERE a.inv_num=v.inv_num
    *
    ERROR at line 1:
    ORA-01407: cannot update ("AFM"."AP_INV"."TOT_1") to NULL

    Can this be done?

    Thanks,
    SBR

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What happens if you modify it a little bit?
    Code:
    UPDATE ap_inv a SET 
    a.tot_1 = (SELECT v.inv_tot_amount + a.tot_1 
               FROM vn_inv v 
               WHERE a.inv_num = v.inv_num 
                 AND a.po_num = v.po_num
              );

  3. #3
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Update a running total field from one table to another

    I get the same message:

    SQL> UPDATE ap_inv a SET a.tot_1 = (SELECT v.inv_tot_amount + a.tot_1 FROM vn_inv v WHERE a.inv_num
    = v.inv_num AND a.po_num = v.po_num);

    UPDATE ap_inv a SET a.tot_1 = (SELECT v.inv_tot_amount + a.tot_1 FROM vn_inv v WHERE a.inv_num = v.i
    *
    ERROR at line 1:
    ORA-01407: cannot update ("AFM"."AP_INV"."TOT_1") to NULL

    SBR

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    No wheres on the update, no thing. It is updating all of the rows. Nvl( <the query>, 0 ) might help as well.

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    you are getting null values back from your subquery.
    are you positive you will always get a match for every row?

    if not, then you first must select only the rows that CAN be updated.
    ie: rows that have a matching:
    a.inv_num = v.inv_num AND a.po_num = v.po_num

    Also don't forget that these two values cannot be null as well:
    v.inv_tot_amount + a.tot_1
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Update a running total field from one table to another

    The values will never be null. Their default value is 0.00 so they will never be null.

    There will be a matching record in the ap_inv table for every record in the vn_inv table. The code that runs before this update statement makes sure there is an entry for each po and invoice that exists in the vn_inv table.

    SBR

  7. #7
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    Could you please explain your comments? I don't understand.

    No wheres on the update, no thing. It is updating all of the rows. Nvl( <the query>, 0 ) might help as well.

    Thanks,
    SBR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •