Update query problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ron - 05 Dec 2005 18:52 GMT

Hi,

I'm trying to update a table with values in another:

UPDATE [Stock Report] SET [Stock Report].Name = (SELECT [Status Assigned
Work].Name
FROM [Status Assigned Work] WHERE [Status Assigned Work].[Serial No] =
[Stock Report].[Serial No]);

I get a error message that the operation must use an updateable query!

Both tables have the same primary key and are not referenced by foreign
keys.

Any help much appreciated.

Regards,
Ron.

Reply to this Message

John Spencer - 05 Dec 2005 19:29 GMT

That syntax will not work in Access. It assumes that the subquery will
return more than one value and automatically decides the query won't work.
Try the following. - If there is more than one match, Access will end up
assigning them in some internal (unknown to me) sequence.

UPDATE [Stock Report] INNER JOIN [Status Assigned Work]
ON [Stock Report].[Serial No] = [Status Assigned Work].[Serial No]
SET [Stock Report].[Name] = [Status Assigned Work].[Name]

You can add a Where clause at the end if you need it.
UPDATE [Stock Report] INNER JOIN [Status Assigned Work]
ON [Stock Report].[Serial No] = [Status Assigned Work].[Serial No]
SET [Stock Report].[Name] = [Status Assigned Work].[Name]
WHERE [Status Assigned Work].[Serial No] = "A123"

One question, why are you doing this? It appears as if you assigning a
value that you already can get from another table. In other words, you are
storing the same data in two places.

> Hi,
>
[quoted text clipped - 14 lines]
> Regards,
> Ron.

Reply to this Message

Ron - 05 Dec 2005 19:51 GMT

> One question, why are you doing this? It appears as if you assigning a
> value that you already can get from another table. In other words, you are
> storing the same data in two places.

One of the tables has some of it's columns imported, the other is used to
store manually updated values (in the first table). When the first table is
imported again (with newish data), some of the rows need to have the
previously updated values inserted (updated!). There is some overlap of data
between imports!

Thanks very much for your advice, the query you suggested seems to work fine.

Regards,
Ron.

Reply to this Message

Ron Carr - 13 Dec 2005 11:37 GMT

>>One question, why are you doing this? It appears as if you assigning a
>>value that you already can get from another table. In other words, you are
[quoted text clipped - 10 lines]
> Regards,
> Ron.

Hi Ron,

This response may relate to someone else, the reason I need to know how
to bulk copy my queries from one database to another, is because the
data I am querying may not be the same, as the data will be aged
(accounts have been closed etc), and the data is periodically moved to
different regions/servers. Hence I would like to know if you can bulk
copy the queries to save having to copy them individually. Or is it
possible to place them somewhere that can be accessed/run from any
database ( eg similar to Excel where you run your macros from the
Startup directory if you copy all your macros there.

Cheers,

Ron.

Reply to this Message

dbahooker@hotmail.com - 27 Dec 2005 17:35 GMT

MDB is crap and too unpredictable for real-world use

it just randomly throws updateable errors and crap like that

screw MDB in the mouth

-aaron

Reply to this Message