SQL Update with an aggregate value

[Rowena]

I am trying to make an update query something like:

UPDATE table1
SET sumValueColumn = sum(ValueColumn)

That gives me the error: You tried to execute a query that does not uncluide
the specified expression sumValueColumn as part of an aggregate function.

I try:

UPDATE table1
SET sumValueColumn = (SELECT sum(ValueColumn) FROM table1)

That gives me the error: Operation must use an updateable query.


I would appreciate any feedback.

Thanks,

 

[Douglas J. Steele]

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

Use a query that calculates the value rather than storing it.

 

[Sylvain Lafontaine]

The expression « UPDATE table1 SET sumValueColumn = (SELECT sum(ValueColumn)
FROM table1) » is a valid SQL-Server T-SQL transact statement; so I suppose
that you are working with Access/JET instead of SQL-Server.

The syntaxe for the Update statement is different between Access/JET and
SQL-Server, so maybe this is where your error come from.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Rowena]

Yes, this is access. Do you know what the correct syntax for access is?

 

[Sylvain Lafontaine]

Yes, this is access. Do you know what the correct syntax for access is?

Sorry but I don't know it. I've also took the time to try some combination
of syntax and I didn't find the correct one. You should ask in a newsgroup
dedicated to Access/JET.

It'a also quite possible that you cannot do that directly in Access; as the
syntax of JET/Access is quite limited when it comes to build complex
queries.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)