'operation must use updatable query' message

[mscertified]

What is wrong with this query, when run it gives 'operation must use
updatable query' message?

UPDATE tblSchools SET DistrictID = (SELECT ID FROM tblSchoolDistricts WHERE
DistrictName = tblSchools.District);

 

[Ken Snell \(MVP\)]

ACCESS / Jet sometimes have problems with UPDATE queries when the primary
key is not in the query. Try this:

UPDATE tblSchools SET DistrictID =
DLookup("ID", "tblSchoolDistricts", "DistrictName = '" &
tblSchools.District & "'");

 

[John Spencer]

Since it is in theory possible for the subquery to return more than one
record, the update query declares that this is not an updateable query.

Try the following - which should be more efficient.

UPDATE tblSchools INNER JOIN tblSchoolDistricts
ON tblSchools.District = tblSchoolDistricts.DistrictName
SET tblSchools.DistrictID = tblSchoolDistricts.ID

Or if that does not work

You can use the DLookup function to get the value

UPDATE tblSchools
SET DistrictID =
DLookup("ID","tblSchoolDistricts","DistrictName=""" & District & """")

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[yungmunk79]

How could I get it to work with this query, where multiple fields represent
the primary index?

UPDATE
qryStationEchoSensors
LEFT JOIN
tblRelateStationEchoSensors
ON
(qryStationEchoSensors.f_sensor_name =
tblRelateStationEchoSensors.SensorName)
AND
(qryStationEchoSensors.f_sensor_code =
tblRelateStationEchoSensors.SensorCode)
AND
(qryStationEchoSensors.f_sensor_ch =
tblRelateStationEchoSensors.SensorChannel)
AND
(qryStationEchoSensors.f_station_code =
tblRelateStationEchoSensors.StationID)
SET
tblRelateStationEchoSensors.LatestReading
=
[qryStationEchoSensors].[LastOfSoil Moisture Reading]
,
tblRelateStationEchoSensors.AverageReading
=
[qryStationEchoSensors].[Reading Average]
WHERE
(((tblRelateStationEchoSensors.LatestReading) Is Null))
OR
(((tblRelateStationEchoSensors.AverageReading) Is Null));