Update one table based on values in another. Complex query.
Posted on 2006-06-20
I have an update that is hurting my brain. I have two tables:
loan (id, start, end, amount)
contrib ( id, date, payment)
for each row in loan there are one or more rows in contrib. Each entry in contrib is a payment on the loan. I need to update loan.start with the earliest date for the corresponding id in the contrib table, and update loan.end with the latest date found in the contrib table for that id.
So I need something like the following that actually works for all rows in loan:
update loan set start = (select min(date) from contrib where id = 'abc'),
end = (select max(date) from contrib where id = 'abc') where id = 'abc'