Updates all Records instead of a subset

LinksUp · #1 (**permalink**) 12-28-12, 18:19

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

PracticalProgram · #2 (**permalink**) 12-28-12, 20:02

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

LinksUp · #3 (**permalink**) 12-28-12, 22:29

Quote:

Originally Posted by PracticalProgram

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.

LinksUp · #4 (**permalink**) 12-29-12, 00:36

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.

LinksUp · #5 (**permalink**) 01-01-13, 01:39

A simple inner join on the table that has the accounts that should be updated does the trick. Duh!

Internet Services

Web Development

Digital Marketing

Consumer Tech