Use SELECT inside an UPDATE query

Question

How can I UPDATE a field of a table with the result of a SELECT query in Microsoft Access 2007   Here s the Select Query   SELECT Min TAX Tax Code  AS MinOfTax Code FROM TAX  FUNCTIONS  WHERE    FUNCTIONS Func Pure  lt   Tax ToPrice   AND   FUNCTIONS Func Year   Tax Year     GROUP BY FUNCTIONS Func ID    And here s the Update Query   UPDATE FUNCTIONS   SET FUNCTIONS Func TaxRef    Result of Select query

User · Accepted Answer

Well  it looks like Access can t do aggregates in UPDATE queries   But it can do aggregates in SELECT queries  So create a query with a definition like   SELECT func id  min tax code  as MinOfTax Code FROM Functions INNER JOIN Tax  ON  Functions Func Year   Tax Tax Year   AND  Functions Func Pure  lt   Tax Tax ToPrice   GROUP BY Func Id   And save it as YourQuery   Now we have to work around another Access restriction  UPDATE queries can t operate on queries  but they can operate on multiple tables   So let s turn the query into a table with a Make Table query   SELECT YourQuery    INTO MinOfTax Code FROM YourQuery   This stores the content of the view in a table called MinOfTax Code   Now you can do an UPDATE query   UPDATE MinOfTax Code  INNER JOIN Functions ON MinOfTax Code func id   Functions Func ID  SET Functions Func TaxRef    MinOfTax Code   MinOfTax Code    Doing SQL in Access is a bit of a stretch  I d look into Sql Server Express Edition for your project

User · Answer

I did want to add one more answer that utilizes a VBA function  but it does get the job done in one SQL statement   Though  it can be slow   UPDATE FUNCTIONS SET FUNCTIONS Func TaxRef   DLookUp  MinOfTax Code    SELECT FUNCTIONS Func ID Min TAX Tax Code  AS MinOfTax Code FROM TAX  FUNCTIONS WHERE    FUNCTIONS Func Pure  lt   Tax ToPrice   AND   FUNCTIONS Func Year   Tax Year    GROUP BY FUNCTIONS Func ID     FUNCTIONS Func ID    amp  Func ID

User · Answer

I had a similar problem  I wanted to find a string in one column and put that value in another column in the same table  The select statement below finds the text inside the parens   When I created the query in Access I selected all fields  On the SQL view for that query  I replaced the mytable myfield for the field I wanted to have the value from inside the parens with   SELECT Left Right OtherField Len OtherField -InStr  OtherField                      Len Right OtherField Len OtherField -InStr  OtherField        -1     I ran a make table query  The make table query has all the fields with the above substitution and ends with INTO NameofNewTable FROM mytable

User · Answer

I wrote about some of the limitations of correlated subqueries in Access JET SQL a while back  and noted the syntax for joining multiple tables for SQL UPDATEs   Based on that info and some quick testing  I don t believe there s any way to do what you want with Access JET in a single SQL UPDATE statement   If you could  the statement would read something like this   UPDATE FUNCTIONS A INNER JOIN     SELECT AA Func ID  Min BB Tax Code  AS MinOfTax Code   FROM TAX BB  FUNCTIONS AA   WHERE AA Func Pure lt  BB Tax ToPrice AND AA Func Year  BB Tax Year   GROUP BY AA Func ID   B  ON B Func ID   A Func ID SET A Func TaxRef   B MinOfTax Code   Alternatively  Access JET will sometimes let you get away with saving a subquery as a separate query and then joining it in the UPDATE statement in a more traditional way   So  for instance  if we saved the SELECT subquery above as a separate query named FUNCTIONS TAX  then the UPDATE statement would be   UPDATE FUNCTIONS INNER JOIN FUNCTIONS TAX ON FUNCTIONS Func ID   FUNCTIONS TAX Func ID SET FUNCTIONS Func TaxRef   FUNCTIONS TAX MinOfTax Code   However  this still doesn t work   I believe the only way you will make this work is to move the selection and aggregation of the minimum Tax Code value out-of-band   You could do this with a VBA function  or more easily using the Access DLookup function   Save the GROUP BY subquery above to a separate query named FUNCTIONS TAX and rewrite the UPDATE statement as   UPDATE FUNCTIONS SET Func TaxRef   DLookup     MinOfTax Code       FUNCTIONS TAX       Func ID       amp  Func ID  amp          Note that the DLookup function prevents this query from being used outside of Access  for instance via JET OLEDB   Also  the performance of this approach can be pretty terrible depending on how many rows you re targeting  as the subquery is being executed for each FUNCTIONS row  because  of course  it is no longer correlated  which is the whole point in order for it to work    Good luck

User · Answer

Does this work   Untested but should get the point across   UPDATE FUNCTIONS SET Func TaxRef        SELECT Min TAX Tax Code  AS MinOfTax Code   FROM TAX  FUNCTIONS F1   WHERE F1 Func Pure  lt    Tax ToPrice      AND F1 Func Year  Tax Year      AND F1 Func ID   FUNCTIONS Func ID   GROUP BY F1 Func ID      Basically for each row in FUNCTIONS  the subquery determines the minimum current tax code and sets FUNCTIONS Func TaxRef to that value   This is assuming that FUNCTIONS Func ID is a Primary or Unique key

User · Answer

I know this topic is old  but I thought I could add something to it   I could not make an Update with Select query work using SQL in MS Access 2010   I used Tomalak s suggestion to make this work   I had a screenshot  but am apparently too much of a newb on this site to be able to post it   I was able to do this using the Query Design tool  but even as I was looking at a confirmed successful update query  Access was not able to show me the SQL that made it happen   So I could not make this work with SQL code alone   I created and saved my select query as a separate query   In the Query Design tool  I added the table I m trying to update the the select query I had saved  I put the unique key in the select query so it had a link between them    Just as Tomalak had suggested  I changed the Query Type to Update   I then just had to choose the fields  and designate the table  I was trying to update   In the  Update To  fields  I typed in the name of the fields from the select query I had brought in   This format was successful and updated the original table

[sql] Use SELECT inside an UPDATE query

Examples related to sql

Examples related to ms-access-2007

Examples related to sql-update