StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I wrote about some of the <a href="http://ewbi.blogs.com/develops/2004/07/accessjet_sql_u.html" rel="noreferrer">limitations of correlated subqueries</a> 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:</p> <pre><code>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 </code></pre> <p>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:</p> <pre><code>UPDATE FUNCTIONS INNER JOIN FUNCTIONS_TAX ON FUNCTIONS.Func_ID = FUNCTIONS_TAX.Func_ID SET FUNCTIONS.Func_TaxRef = FUNCTIONS_TAX.MinOfTax_Code </code></pre> <p>However, this still doesn't work.</p> <p>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:</p> <pre><code>UPDATE FUNCTIONS SET Func_TaxRef = DLookup( "MinOfTax_Code", "FUNCTIONS_TAX", "Func_ID = '" & Func_ID & "'" ) </code></pre> <p>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).</p> <p>Good luck!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload