Update from Max value

Question

Post reply

Update from Max value

rmcknight

SSC Eights!

Points: 918
More actions
October 26, 2007 at 2:30 pm

#62875

Another Brain fart.
Table_A Code, Price
Table_B Code , Eff_date, Price
Update Table_A Set Price = Table_B.Price where MAX(Table_B.Eff_Date)
If Table_A Code & Price
CODE1, 20.00
COde2, 30.00
And Table_B Code, Eff_Date & Price
CODE1, 1/1/2004, 20.00
CODE1, 2/5/2007, 30.00
CODE2, 1/1/2004, 20.00
CODE2, 3/1/2006, 40.00
CODE2, 7/1/2007, 25.00
Then After Update TABLE_1 Code, Price
CODE1, 30.00
CODE2, 25.00
I know, beginner, but it IS Friday.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

use a derived table

select code,price from tableb join

(select code,max(date) from tableb group by code) tbmd

on tbmd.code = tableb.code and tableb.eff_date = tbmd.date

and join it to tablea

Antares686 SSC Guru Points: 125444 More actions · Answer 2

UPDATE

A

SET

Price =B.Price

FROM

Table_A A

INNER JOIN

Table_B B

INNER JOIN

(SELECT Code, Max(EFF_DATE) Max_Eff_Date FROM Table_B GROUP BY Code) C

ON

B.Code = C.Code AND

B.EFF_DATE = C.Max_Eff_Date

ON

A.Code = B.Code

rmcknight SSC Eights! Points: 918 More actions · Answer 3

This is bringing back dup's. The right price, but assigned for number of dates found in table B.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

works for me.

what does this bring back for you?

select a.code,a.price, b.code,b.price from table_b b join

(select code,max(eff_date) eff_date from table_b group by code) tbmd

on tbmd.code = b.code and b.eff_date = tbmd.eff_date

join table_a a on b.code = a.code

Antares686 SSC Guru Points: 125444 More actions · Answer 5

The only way you should get dupes with mine is you have multiple dates the same per code. If that is the case then how do you propose to resolve selection?

rmcknight SSC Eights! Points: 918 More actions · Answer 6

My Mistake.

The duplicate's orgininate in Table_A, which is ok. The fact they are getting adsigned max(price) looks right.

Thanks.

Randy.