Simple SQL (MS Access...) question

ddhoward · **Author**

People here know SQL, right? Right?

Alright, so I'm simply trying to update all null entries in ColumnA of TableA with the largest value found in ColumnB of TableB.

What do.

This is what Access auto-generates.

PHP Code:


			
UPDATE TableA SET TableA.ColumnA = Max([TableB].[ColumnB])
WHERE (((TableA.ColumnA) Is Null));

Quote:

Your query does not include the specified expression 'ColumnA' as part of an aggregate function.

I honestly have no idea what I'm doing but this is the finishing touch on a project due tomorrow.

asherkin · *Last edited by asherkin; 05-10-2017 at 05:47.*

I have no fucking idea of the MS Access SQL dialect, and I'm not really sure I understand what you're looking for, but from a bit of reading the reference, try something like this (backup first!):

Code:

UPDATE TableA SET TableA.ColumnA = (SELECT Max(TableB.ColumnB) FROM TableB)
WHERE (((TableA.ColumnA) Is Null));

Basically, make getting the max of ColumnB using a subquery, so it doesn't try and evaluate the aggregate as part of the outer query.

klippy · *Last edited by klippy; 05-10-2017 at 05:48.*

I don't know if that would cause the error, but you don't need any of the parenthesis around "TableA.ColumnA IS Null". I've worked with MS SQL lately and I've noticed it's a bit different to MySQL with where it requires parenthesis.

gabuch2 · 05-10-2017 , 07:51 Re: Simple SQL (MS Access...) question

Code:

UPDATE TableA SET ColumnA = (SELECT MAX(ColumnB) FROM TableB) WHERE ColumnA IS NULL;

ddhoward · *Last edited by ddhoward; 05-10-2017 at 17:12.*

Access returned an astoundingly unhelpful error message when I tried the above suggestion(s). The way I ended up having to accomplish this was incredibly asinine and convoluted, taking several hours to tackle. It involves macros, hidden named calculated fields on forms that only exist for this purpose, and a total of three queries.

Thanks for the help dudes. I certainly know more about SQL after taking this Access course, but I never want to use this god damn program ever again.

asherkin · 05-10-2017 , 17:21 Re: Simple SQL (MS Access...) question

Good! Use a real SQL database!