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

    Unanswered: Update single row in table 1 from multiple rows in another table

    I have a table called ap_po that keeps running totals for purchase orders so that we can keep track of how much is spent. I am writing code to automate the data entry of the invoices that updates this table. Currently I am using this code to update the running totals for individual jobs in this table. (There is only one record per job number per Purchase Order.) Here is the code that updates the totals:

    sSql = "UPDATE ap_po set (Tot_3) = NVL((SELECT ap_po.Tot_3 + vn_inv.Inv_Tot_Amount FROM vn_inv WHERE " _
    & "vn_inv.duplicate IS Null AND ap_po.po_num = vn_inv.po_num AND " _
    & "ap_po.wr_id = vn_inv.wr_id ),Tot_3) WHERE ap_po.po_num in (SELECT vn_inv.po_num FROM vn_inv) " _
    & "AND ap_po.wr_id in (SELECT vn_inv.wr_id FROM vn_inv)"

    It works great. Now I need to update the totals in the same table for records that have a job number of 0.00. The table that I update from may have more than one record that would have a matching purchase order number for job number 0.00. I tried using a similar line of code like the code above to update the totals, but I get the message "single-row subquery returns more than one row."

    sSql = "UPDATE ap_po set (Tot_2) = NVL((SELECT ap_po.Tot_2 - vn_inv.Inv_Tot_Amount FROM vn_inv WHERE " _
    & "vn_inv.duplicate IS Null AND ap_po.po_num = vn_inv.po_num AND " _
    & "ap_po.wr_id = 0.00 ),Tot_2) WHERE ap_po.po_num IN (SELECT po_num FROM (select vn_inv.po_num FROM vn_inv) "

    In my search for an answer, I found out that a subquery can only return one row. How can I update the totals in the ap_po table where the job number is 0.00 and there are multiple records in the other table to update them?

    Thanks,
    SBR

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Use SUM() function.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

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

    Update single row in table 1 from multiple rows in another table

    I tried the sum() function as you suggested and I didn't get the error message but it only adds the first record it finds to the total. Here's the code I tried:

    UPDATE ap_po set (Tot_2) = NVL((SELECT ap_po.Tot_2 - sum(vn_inv.Inv_Tot_Amount) FROM vn_inv WHERE vn_inv.duplicate IS Null AND ap_po.po_num = vn_inv.po_num AND ap_po.wr_id = 0.00),Tot_2) where ap_po.po_num in (SELECT vn_inv.po_num FROM vn_inv);

    Any idea what I need to do from here?

    Thanks,
    SBR

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Are there actually NULLs in the amount?


    Try this:
    Code:
    Update Ap_Po Set Tot_2 = Ap_Po.Tot_2 - (
        Select Sum(NVL(Vn_Inv.Inv_Tot_Amount,0)) From Vn_Inv
         Where Vn_Inv.Duplicate Is Null
           And Ap_Po.Po_Num = Vn_Inv.Po_Num
           And Ap_Po.Wr_Id = 0.00)
      Where Ap_Po.Po_Num In (Select Vn_Inv.Po_Num From Vn_Inv);


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    UPDATE ap_po set (Tot_2) = (SELECT nvl(sum(vn_inv.Inv_Tot_Amount),0)
                                FROM vn_inv 
                                WHERE vn_inv.duplicate IS Null 
                                AND ap_po.po_num = vn_inv.po_num 
                                AND ap_po.wr_id = 0.00)
    where ap_po.po_num in (SELECT vn_inv.po_num 
                           FROM vn_inv);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

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

    Update single row in table 1 from multiple rows in another table

    Sorry, I was wrong. It does work. One of my records had a 'Y' in the duplicate field and it was excluding it. Once I took the "Y' out, both records were added to the total.

    Thanks for your help!

    SBR

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by beilstwh
    Code:
    UPDATE ap_po set (Tot_2) = (SELECT nvl(sum(vn_inv.Inv_Tot_Amount),0)
                                FROM vn_inv 
                                WHERE vn_inv.duplicate IS Null 
                                AND ap_po.po_num = vn_inv.po_num 
                                AND ap_po.wr_id = 0.00)
    where ap_po.po_num in (SELECT vn_inv.po_num 
                           FROM vn_inv);

    If the amount column vn_inv.Inv_Tot_Amount has NULL values, then SUM(vn_inv.Inv_Tot_Amount) will produce incorrect result.


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

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

    Update single row in table 1 from multiple rows in another table

    Thanks for the "heads up" on the null value.


    Inv_tot_amount can never be null. It will have a default value of 0.00.


    SBR

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by sbr7770
    Thanks for the "heads up" on the null value.


    Inv_tot_amount can never be null. It will have a default value of 0.00.


    SBR

    It's allways good to use the NVL() function when you suspect there are NULLs in a numeric column and you are using an aggregate function (like SUM()) -- a NULL will give you incorrect result.


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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