SQL Server Performance

Update Header with summed detail

Discussion in 'SQL Server 2005 General Developer Questions' started by tomar, Mar 31, 2009.

  1. tomar New Member

    I have a header table with vendor number, invoice number, invoice date as the key fields and invoice amount and tax amount as data fields.
    I have a detail table with vendor number, invoice number,invoice date, account number as the key and account amount as the data.
    I want to update the tax amount in the header with the results from the following query:SELECT tbl_Inv_Detail.Vendor_No, tbl_Inv_Detail.Invoice_No, tbl_Inv_Detail.Invoice_Date,
    SUM(CASE tbl_Taxes.Type WHEN 'G' THEN tbl_Inv_Detail.GL_Amount ELSE 0 END) AS GST, FROM
    tbl_Inv_Detail LEFT OUTER JOIN tbl_Taxes ON tbl_Inv_Detail.GL_Account = tbl_Taxes.Tax_ID GROUP
    BY tbl_Inv_Detail.Vendor_No, tbl_Inv_Detail.Invoice_No, tbl_Inv_Detail.Invoice_Date
    I have been trying to find a way to code this so my query would look something like this:
    UPDATE tbl_Inv_Header SET tbl_Inv_Header.Tax = GST FROM (select query )
    but I am not completely sure of the syntax. could some one help?
    Thanks,
    Tom
  2. Madhivanan Moderator

    UPDATE
    H SET
    H.Tax = d.GST from
    tbl_Inv_Header as H inner join (select query) as don
    H.Invoice_no=D.Invoice_no and ....
  3. tomar New Member

    Thanks,
    That worked
  4. Madhivanan Moderator

    [quote user="tomar"]
    Thanks,
    That worked
    [/quote]
    You are welcome [:)]

Share This Page