Update Query Based on Latest Dates

[lambic]

Hi,
I have an to write an update query, based on dates, which I can't quite figure out. The details are:

Table A:
AccountID
DateFrom
DateTo
Value

Table B:
AccountID
DateFrom
DateTo
Value

What I need to do is update Table A Value with the Value from Table B where the AccountID's match and the DateTo in Table B is not later than that in Table A (i.e. there can be multiple records in B, so I want the latest for each account that's not later than that AccountID's entry in table A).
I think that makes sense!

Any help would be appreciated.

Cheers

 

[r937]

update syntax varies from one database to another

your best bet would be to ask this question in the appropriate forum



r937.com | rudy.ca

 

[JarlH]

Untested:

[tt]
UPDATE TableA
SET Val = (SELECT MAX(Val) FROM TableB
WHERE TableB.AccountID = TableA.AccountID
AND TableB.DateTo = (SELECT MAX(TableB.DateTo) FROM TableB
WHERE TableB.AccountID = TableA.AccountID
AND TableB.DateTo <= TableA.DateTo))[/tt]

ISO/ANSI core SQL-2003.

 

[lambic]

Thanks guys - will give both suggestions a try (the database is Ingres by the way).

Cheers