sql question - if you allow...

HoMa

I know, this is not a perfect match for this forum, however, maybe someone has an idea:

Two tables:
contracts with fields: id, firstBooking (note: firstBooking is a newly added field and therefor empty)
bookings with fields:ContractId, bookingDate

with smthg like
SELEcT contractId, MIN(bookingDate) FROM bookings
I get a list with all first bookings.
Now I want to store these dates in the contracts table field "firstBooking" - with the matching contract, of course.
Is there a way to do this without programming a loop - just in one SQL statement? I tried a lot ...

HoMa

OK - never mind. Finally I did it: One needs to have a UPDATE clause where there is a FROM part, which basically extracts the minimal booking date and a WHERE part, that compares the Contract Ids
So it is

UPDATE Vertraege
  SET firstBooking = bookings.minBDate

FROM ( SELEcT Vertraege.id AS VertragsId, MIN(Datum) AS minBDate
FRoM Buchungen Join Vertraege on Vertraege.id = Buchungen.VertragsId
GROUP BY VertragsId) AS bookings

WHERE Vertraege.id = bookings.VertragsId

Just in case someone comes across smthg similar. The UPDATE SQL statement is pretty powerful, but not always easy to master ;)