Update one table based on values in another. Complex query.

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'

LVL 1
jmarkfoleyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
acampomaConnect With a Mentor Commented:
Update loan set Start=MINDate, [End]=MaxDate from loan a Join
(select id,min(date)as MINDate, Max(date) as MaxDate from contrib group by Id) b
on a.Id=b.Id
0
 
acampomaCommented:
Update loan set Start=MINDate, End=MaxDate from loan a Join
(select id,min(date)as MINDate, Max(date) as MaxDate from contrib group by Id) b
on a.Id=b.Id
0
 
nicolasdiogoCommented:
here is my take on the subject

update loan set

startDate = min( contrib.date ) ,
endDate = max( contrib.date )

from
  loan
, contrib

where

loan.id = contrib.id

let me know if it works

nicolas

www.brainpowered.net
0
 
jmarkfoleyAuthor Commented:
nicolasdiogo: yours didn't work. Got the error: An aggregate may not appear in the set list of an UPDATE statement.

 acampoma: yours worked!
0
 
nicolasdiogoCommented:
update loan set

  startDate = src.startDate
, endDate = src.endDate

from
(
select
  startDate = min( contrib.date )
, endDate = max( contrib.date )
from
  loan
, contrib
where
loan.id = contrib.id
) src

0
All Courses

From novice to tech pro — start learning today.