Use SELECT inside an UPDATE query

by

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

More Related Contents:

  1. The most efficient way to UPPER CASE two columns in a single query [closed]
  2. How can I do an UPDATE statement with JOIN in SQL Server?
  3. How do I (or can I) SELECT DISTINCT on multiple columns?
  4. SQL update from one Table to another based on a ID match
  5. SQL update query using joins
  6. How to insert a value that contains an apostrophe (single quote)?
  7. Oracle SQL: Update a table with data from another table
  8. updating table rows in postgres using subquery
  9. how can I Update top 100 records in sql server
  10. SQL UPDATE order of evaluation
  11. How to update and order by using ms sql
  12. Casting NULL type when updating multiple rows
  13. How to write UPDATE SQL with Table alias in SQL Server 2008?
  14. Slow simple update query on PostgreSQL database with 3 million rows
  15. SQL Server Update Trigger, Get Only modified fields
  16. Oracle Update Query using Join
  17. Update with Join query in Oracle
  18. SQL update fields of one table from fields of another one
  19. SQL Server Update Group by
  20. I want to use CASE statement to update some records in sql server 2005
  21. Bulk/batch update/upsert in PostgreSQL
  22. Postgres error updating column data
  23. Update a column of a table with a column of another table in PostgreSQL
  24. Functions with variable number of input parameters
  25. Oracle: Updating a table column using ROWNUM in conjunction with ORDER BY clause
  26. how to increment integer Columns value by 1 in SQL
  27. T-SQL: Using a CASE in an UPDATE statement to update certain columns depending on a condition
  28. Update rows in one table with data from another table based on one column in each being equal
  29. SQL Update woes in MS Access – Operation must use an updateable query
  30. Update a table with data from another table

Leave a Comment