UPDATE SQL - shows updating more then required record?

[Ken]

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.

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

 

[simian336]

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

 

[Ken]

T1.TrDate >= T2.EffectiveDate displays update will be 2130
T2.EffectiveDate >= T1.TrDate displays update will be 1302

 

[SkipVought]

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]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Ken]

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 ?