Update query question

[Tim]

I'm trying to run an update query but keep getting an
error of "Operation must use an updateable query".

Im lost - my update query is trying to chage rows of a
table to the content of another table, so the 'updaate
to' fields reads 'SELECT * from TEMP;' - there will only
ever be one record in this table with one field.
But still the error!

SQL below, pointers appreciated
TIA
Tim
UPDATE Temp_Staff_Status SET Temp_Deploy_Status.Tool_ID =
(SELECT TMP_New_Tool.Tool_ID
FROM TMP_New_Tool;

 

[Ted Allen]

Hi Tim,

Well, you know that there will only be one record in the
TMP table, but Access doesn't know that. In order to use
a subquery as the "Update To" value, it has to be written
such that it will always return a single value,
regardless of how many records may be in the source
table. Usually this will mean using First, Min, Max,
Count, Sum, etc.

But, it appears that there is problem with the query sql
as well. The table name after update is
Temp_Staff_Status, but then the table name preceeding the
Tool_ID field after the SET statement is
Temp_Deploy_Status. The table being updated should be
part of your original table list (sometimes the only one,
sometimes joined with other tables).

Finally, I'm not sure if you can have the semi-colon in
your subquery. I know that I don't include it in mine,
but I've never tried it with one. In any case it won't
hurt to remove it.

Following is an example of valid update query sql text
(if I haven't made any typos), but I'm not sure if it
will work for you as I'm not sure which table you are
actually trying to update.

UPDATE Temp_Deploy_Status SET Temp_Deploy_Status.Tool_ID
= (SELECT First(TMP_New_Tool.Tool_ID)
FROM TMP_New_Tool);

Note that this will update the Tool_ID field for all
records in the Temp_Deploy_Status table since there are
no criteria restricting which records should be updated.

As always, it is a good idea to back up your database
before trying new action queries.

HTH, Ted Allen