Update by most recent date

[tcb]

I have a query the results of which are below. I would like to update
any wClass of the most recent wDate for that xID. How can I do this.
Thanks.

For example, in the case of 01111140064 I would like all wClass = 5.
for 01111562169 I would llike the wClass = 30

wDate wID xID wClass
2008-03-15 00:00:00 01111140064 1 5
2008-03-08 00:00:00 01111140064 1 5
2008-03-01 00:00:00 01111140064 1 5
2008-02-23 00:00:00 01111140064 1 5
2008-02-16 00:00:00 01111140064 1 5
2008-02-09 00:00:00 01111140064 1 5
2008-02-02 00:00:00 01111140064 1 5
2008-01-26 00:00:00 01111140064 1 34
2008-01-19 00:00:00 01111140064 1 34
2008-01-12 00:00:00 01111140064 1 34
2008-01-05 00:00:00 01111140064 1 34
2006-03-11 00:00:00 01111562169 14 15
2006-03-04 00:00:00 01111562169 14 15
2008-03-08 00:00:00 01111562169 14 30
2008-03-01 00:00:00 01111562169 14 30
2007-12-29 00:00:00 49999102585 11 32
2008-02-09 00:00:00 49999102585 11 34

 

[Wayne-I-M]

Hi

for 01111562169 I would llike the wClass = 30

But the most recent Wdate for 01111562169 (the Xid) is 2006-03-11
so the result would be 15 ??

 

[John W. Vinson]

I have a query the results of which are below. I would like to update
any wClass of the most recent wDate for that xID. How can I do this.
Thanks.

For example, in the case of 01111140064 I would like all wClass = 5.
for 01111562169 I would llike the wClass = 30

wDate wID xID wClass
2008-03-15 00:00:00 01111140064 1 5
2008-03-08 00:00:00 01111140064 1 5
2008-03-01 00:00:00 01111140064 1 5
2008-02-23 00:00:00 01111140064 1 5
2008-02-16 00:00:00 01111140064 1 5
2008-02-09 00:00:00 01111140064 1 5
2008-02-02 00:00:00 01111140064 1 5
2008-01-26 00:00:00 01111140064 1 34
2008-01-19 00:00:00 01111140064 1 34
2008-01-12 00:00:00 01111140064 1 34
2008-01-05 00:00:00 01111140064 1 34
2006-03-11 00:00:00 01111562169 14 15
2006-03-04 00:00:00 01111562169 14 15
2008-03-08 00:00:00 01111562169 14 30
2008-03-01 00:00:00 01111562169 14 30
2007-12-29 00:00:00 49999102585 11 32
2008-02-09 00:00:00 49999102585 11 34

You can use the DMax() function to find the most recent date for each xID:

UPDATE yourtable
SET wClass = 5
WHERE wID = "01111140064"
AND wDate = DMax("wDate", "tablename", "wID='01111140064'")