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 · 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<=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 = '" & Func_ID & "'"
)

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

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

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 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

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 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