AlliedModders - Simple SQL (MS Access...) question

AlliedModders (https://forums.alliedmods.net/index.php)

- Off-Topic (https://forums.alliedmods.net/forumdisplay.php?f=15)

- - Simple SQL (MS Access...) question (https://forums.alliedmods.net/showthread.php?t=297269)

ddhoward

05-10-2017 05:22

Simple SQL (MS Access...) question

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

05-10-2017 05:46

Re: Simple SQL (MS Access...) question

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

05-10-2017 05:47

Re: Simple SQL (MS Access...) question

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

05-10-2017 17:10

Re: Simple SQL (MS Access...) question

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! :P

All times are GMT -4. The time now is 06:43.