Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    162

    Updates all Records instead of a subset

    I am trying to update a small subset of records of a given table (TRValue) using the records contained in ParcelTemp. The difficult part is getting the summation from a child file, TRGreen, for those same parcels contained in ParcelTemp. Instead of updating just a few records, all the records in TRValue are being updated, with the wrong values of course!

    Basically, Update records in TRValue that are equal to:
    Year = P.Year
    Code = 'LG01'
    Parcel = P.Parcel

    with the summation of child records
    where the child records needed are:
    Year = P.Year
    Parcel = P.Parcel

    If I have missed something in my explanation, please let me know and I will clarify.

    Thanks.


    Code:
    UPDATE TRValue SET
       Acres = SumAcres,
       CurrentMarket = SumMarket,
       CurrentTaxable = SumTaxable,
       CurrentTaxAmt = ((SumTaxable * D.CertifiedRate) + 0.50)
      FROM ParcelTemp  P
      JOIN 
    (
     SELECT Year, Parcel,
      SUM(G.Acres) as SumAcres,
      SUM(G.Market) as SumMarket,
      SUM(G.Taxable) as SumTaxable
      FROM TRGreen G
     WHERE G.Status = 0 
     GROUP BY G.Year, G.Parcel
    ) G1 on G1.Year = V.Year and G1.Parcel = V.Parcel
     INNER JOIN TRMaster M on M.Year = V.Year and M.Parcel = V.Parcel
     INNER JOIN TRDistrict D on D.Year = M.Year and D.Code = M.District
     WHERE P.Year = 2012 and P.Code = 'LG01' and P.Parcel = G1.Parcel

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    How are the records in the TRValue table related to the records in any of the other tables you are using?

    Whenever I write an UPDATE query, I first write it as a SELECT query, so I can validate that it is filtering for the records I'm interested in. Only after I am satisfied that it is filtering correctly, do I move on to converting it to an UPDATE query.

    Additionally, I NEVER write an UPDATE query that does not include the table I am updating as one of the source tables. In your query, you are not referencing the TRValue table except in the UPDATE clause. I would always have the table you are updating as the FROM table, with all the relationships to other tables clearly defined via joins.

    I am sure the methodology you are using is acceptable to many, but I think my methodology yields a clearer picture of what is happening in the query.

    Reformatted it a bit to make it more understandable to me.

    Just my two cents. Good luck.

    Code:
    UPDATE  TRValue
    SET     Acres = SumAcres,
            CurrentMarket = SumMarket,
            CurrentTaxable = SumTaxable,
            CurrentTaxAmt = ((SumTaxable * D.CertifiedRate) + 0.50)
    --SELECT   *
    FROM    ParcelTemp  P
    JOIN    (
            SELECT  Year,
                    Parcel,
                    SUM(G.Acres) as SumAcres,
                    SUM(G.Market) as SumMarket,
                    SUM(G.Taxable) as SumTaxable
            FROM    TRGreen G
            WHERE   G.Status = 0 
            GROUP
            BY      G.Year,
                    G.Parcel
            ) G1 on
                    G1.Year = V.Year
                    and G1.Parcel = V.Parcel
    INNER
    JOIN    TRMaster M on
                    M.Year = V.Year
                    and M.Parcel = V.Parcel
    INNER
    JOIN    TRDistrict D on
                    D.Year = M.Year
                    and D.Code = M.District
    WHERE   P.Year = 2012
            and P.Code = 'LG01'
            and P.Parcel = G1.Parcel
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    162
    Quote Originally Posted by PracticalProgram View Post
    How are the records in the TRValue table related to the records in any of the other tables you are using?

    Whenever I write an UPDATE query, I first write it as a SELECT query, so I can validate that it is filtering for the records I'm interested in. Only after I am satisfied that it is filtering correctly, do I move on to converting it to an UPDATE query.
    Good suggestion.

    Let me start with the general queries and then move to the specifics.

    This is the query that gets all possible parent records:

    Code:
    SELECT
        V.Year, V.Parcel, V.Acres, 
        V.CurrentMarket, V.CurrentTaxable, V.CurrentTaxAmt 
    FROM TRValue V
    WHERE V.Year = 2012 and V.Code = 'LG01'

    And this is the query that returns all the child records with summations on the appropriate fields:

    Code:
    SELECT Year, Parcel,
      SUM(G.Acres) as SumAcres,
      SUM(G.Market) as SumMarket,
      SUM(G.Taxable) as SumTaxable
      FROM TRGreen G
     WHERE G.Year = 2012 and G.Status = 0 
     GROUP BY G.Year, G.Parcel
    The general idea is to update the parent record with the summation of its child records.

    The *extras* that make it more difficult include:

    1. The CurrentTaxAmt in the parent record is a calculation based on the last two inner joins of the original post.

    Code:
    INNER JOIN TRMaster M on M.Year = V and M.Parcel = V.Parcel
    INNER JOIN TRDistrict D on D.Year = M.Year and D.Code = M.District
    . . . 
    (part of the update statement)
    V.CurrentTaxAmt = V.CurrentTaxable * D.CertifiedRate
    2. It would be nice if there was a way to have the query use a table that holds a list of accounts that should be updated. Rather than update every parent record, just do the ones in the "other" table.

    The linking fields, as you may have gathered by now, include Year and Parcel.

    Thanks for your suggestions.

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    162
    I got the first part taken care. I got a query that works on the whole table and updates it properly.

    Here is the query to update a parent record with a summation of child records:

    Code:
    UPDATE V SET
       v.Acres = g1.SumAcres,
       v.CurrentMarket = g1.SumMarket,
       v.CurrentTaxable = g1.SumTaxable,
       v.CurrentTaxAmt = ((g1.SumTaxable * D.CertifiedRate) + 0.50)
     FROM TRValue V
    	 INNER JOIN
              (SELECT G.Year, G.Parcel,
    		SUM(G.Acres) as SumAcres,
    		SUM(G.Market) as SumMarket,
    		SUM(G.Taxable) as SumTaxable
    		FROM TRGreen G
    	       WHERE G.Year = 2012 and G.Status = 0
    	       GROUP BY G.Year, G.Parcel
               ) g1 on g1.year = v.Year and g1.parcel = v.Parcel
     INNER JOIN TRMaster M on M.Year = V.Year and M.Parcel = V.Parcel
     INNER JOIN TRDistrict D on D.Year = M.Year and D.Code = M.District
     WHERE V.Year = 2012 and V.Code = 'LG01'
    This leaves the next question to be answered.

    Is there a way to feed this query a table with a list of parcels (accounts) to work on rather than the whole parent table?

    Thanks.

  5. #5
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    162
    A simple inner join on the table that has the accounts that should be updated does the trick. Duh!

Posting Permissions

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