If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Updates all Records instead of a subset

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On