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 for the update. 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
WHERE ((SummaryTable.CarType ="Sedan") AND (SummaryTable.NumberDrivers="NumberDrivers1") AND (SummaryTable.NumberClaims="NumberClaims1")));