Clever Geek Handbook

SQL: batch table updates based on MIN of another? Probably easy but stuck

I think it will be easy, but I can't see how!

I want to update the authors table so that the earliest field contains the year of the earliest book. I need to use MINI somehow, but how do I update them all with one request?

BookTable (BookID, AuthorFK, Year)

01 34 1943
02 34 1933
03 99 1910
04 62 1990
05 99 1901

AuthorTable (AuthorID, earliest)

34 1933
62 1990
99 1901

EDIT: HA HA WORKS TOTALLY THANKS FOR A SUPERIOR ANSWER! STACKO RULES (EXCEPT OPEN THINGS)

0


source to share


2 answers


update AuthorTable set Earliest = (select min (Year) from BookTable where BookTable.AuthorFK = AuthorTable.AuthorID)



+2


source


I would suggest doing this with a merged subquery that has a grouping like:



update AuthorTable set Earliest = bt. [Year] From AuthorTable as At Join (select AuthorFK, min (Year) as [Year] from Booktable group from AuthorFK) as bt on at.AuthorID = bt.AuthorFK

+1


source






More articles:


All Articles