Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

UPDATE SQL - shows updating more then required record?

Status
Not open for further replies.

Ken

IS-IT--Management
Jul 13, 2005
68
CA
Hi to all,

How to make sure below SQL is updating right price ?
Need to update only 1000 transaction with correct price, on or after effective date.

There are 1000 transaction and on running below SQL query, message shows updated 2130 transaction record.

Code:
Update     TransactionTbl T1
INNER JOIN PriceTbl       T2 on T1.ProductId = T2.ProductId
set    T1.price = T2.price
where  [COLOR=#EF2929]T1.TrDate > = T2.EffectiveDate[/color]

Table: TransactionTbl
TrDate    ProductId Price
4/30/2018   P0001     $19
5/ 1/2018   P0001     $20
5/ 2/2018   P0001     $20
5/ 4/2018   P0001     $22

Table:  PriceTbl
ProductId EffectiveDate  Price
P0001      5/1/2018        $20
P0001      5/3/2018        $22

Please guide/help.

Thank you,

Ken
 
so how many records do you get if you run this...

select T1.price, T1.TrDate, T2.price, T2.EffectiveDate
from TransactionTbl T1
INNER JOIN PriceTbl T2
on T1.ProductId = T2.ProductId
where T1.TrDate > = T2.EffectiveDate

Simi
 

T1.TrDate >= T2.EffectiveDate displays update will be 2130
T2.EffectiveDate >= T1.TrDate displays update will be 1302
 
There are 1000 transaction and on running below SQL query, message shows updated 2130 transaction record.

[pre]
where T1.TrDate > = T2.EffectiveDate

Table: TransactionTbl
TrDate ProductId Price
4/30/2018 P0001 $19
5/ 1/2018 P0001 $20
5/ 2/2018 P0001 $20
5/ 4/2018 P0001 $22

Table: PriceTbl
ProductId EffectiveDate Price
P0001 5/1/2018 $20
P0001 5/3/2018 $22
[/pre]

You have 4 transactions.
The first price update will change the 3 transactions with TrDates >= 5/1/2018
The second price update will change the 1 transaction with TrDates >= 5/3/2018

In this small sampling, with only 3 transactions being updated but having 4 updates occurring, it is conceivable that 2130 updates is not surprising in 1000 transactions. Depending on the actual PriceTbl, transactions could be updated multiple times. In fact, I might want to Order By EffectiveDate Asc prior to the update step.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
Hi Skip,

Tried performing order by effective date and then update.
However it showed updating with different rate.

It seems best to follow
1) Group each effective date on descending order
2) Then update rate on group of each effective date descending order
with TrDate > = EffectiveDate

similar to sub query, is there way to first group on EffectiveDate desc and
then run update SQL ?



 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top