0

I'm having some tables below:

Table A, with FundID, and Fund Type

FundID Fund Type
A Multi
B Prop

Table B, with Fund, properties that are in the fund, and fund interest. One property could be in multiple funds, with different percentage but should add up to 100%

PropertyID FundID Fund Percentage
1 A 60%
1 B 40%
2 B 100%

Table A, where the Fund Type is not known. I'm trying to grab the Fund Type, where the percentage is largest for the property. In the example above, Property 1 belong primarily to Fund A, so the Fund type below would be Multi.

PropertyID Fund Type
1 ?
2 ?

I have tried many different SQL commands I found on Google but maybe my direction is just not right. I have gotten as far as writing a SELECT for the above example so that the fund type turns out to be Multi for Property 1, but failed to write the UPDATE command on top of it. I'd appreciate any help - thank you!

Tom Dang
  • 1
  • 1
    Any SQL that involves aggregate query cannot be used in an UPDATE action. You haven't even shown any attempted code. You should explore TOP N query. – June7 Aug 11 '23 at 00:49
  • Review https://stackoverflow.com/questions/76859762/how-to-see-the-month-with-the-top-number-for-every-year-in-microsoft-access-sql/76862320#76862320 – June7 Aug 11 '23 at 02:56
  • it's clear you put effort into your question, so here is my response. Go back and review table normalization. Then make the tables Properties, Funds, PropertiesFunds and hook up the relationships. This sets up a many to many relationship. Then if you get stuck again inserting data into the PropertiesFunds Table you should be able to ask a question I have a better chance of being able to answer. – mazoula Aug 11 '23 at 04:19
  • I may be wrong but am pretty sure this could be done using `MAX()` with a `WHERE` clause for PropertyId as a subquery for the `SET` value (no `GROUP BY`) and an external `WHERE` for the PropertyId. No `JOIN` needed Research correlated subqueries. – bugdrown Aug 11 '23 at 12:06
  • Here's a simple example: https://data.bangtech.com/sql/sql_update_with_correlated_subquery.htm – bugdrown Aug 11 '23 at 15:52
  • Saving calculated (data dependent on other data) data is usually unnecessary and often bad design. If it can be calculated for UPDATE it can be calculated when needed. @bugdrown, your suggestion does work - you could write an answer. Note that TOP N accomplishes same result. – June7 Aug 12 '23 at 17:25

0 Answers0