Update table with inner select

[Andy camp]

Hello,

I'm new to databases and trying to run the below query.

When I run the query the following message is displayed
Undefined function SummaryTable.Total in expression

How to retrieve the value for SummaryTable.Total to use in the update.



My aim is to retrieve the relevant value for total from the SummaryTable (inner select) to use in the calculation. The text value for CarType, NumberDrivers and NumberClaims from the SummaryTable are together used as the primary key.

The table profile data doesn't share a link with the summary table. I want to be able to perform the calculation on all records ([ProfileData].CarValueO1 = [ProfileData].CarValue * SummaryTable.Total)

UPDATE [ProfileData] SET [ProfileData].CarValueO1 = [ProfileData].CarValue * SummaryTable.Total
(SELECT SummaryTable.Total
FROM SummaryTable
WHERE ((SummaryTable.CarType ="Sedan") AND (SummaryTable.NumberDrivers="NumberDrivers1") AND (SummaryTable.NumberClaims="NumberClaims1")));

SummaryTable structure:

CarType Text
NumberDrivers Text
NumberClaims Text
Total Number

Thanks for your help.

[June7]

Why are you saving aggregate data? This is usually a bad idea. Aggregate data can be calculated when needed.

If you want the total of all records in SummaryTable, must do an aggregate (Group By) query or use DSum() domain aggregate function.