Why is this a non updateable query?

[Phil]

UPDATE item INNER JOIN [Fifty special items to lowercase] ON
item.item_id = [Fifty special items to lowercase].item_id SET
item.active = "x";


Item is a table that I have full rights to. Item_id is unique.

[Fifty special items to lowercase] is a query that returns 50 unique
items from the item table that meet certain criteria.

There is a one to one matchup between the 50 items, and the Item table
which has many thousands of items.

Should be a simple update. Not sure why it fails. Any ideas?

 

[John Spencer]

Can't tell from this, but I suspect that the Fifty query is not updatable for
some reason. You might get around this by using

Update Item
SET Item_id = "x"
WHERE Item.Id in
(SELECT Item_ID
FROM [Fifty special items to lowercase])

You can often use an non-updateable query in the where clause and still have a
working Update query.

 

[Phil]

Perhaps you could enlighten me a little bit. Yes, the Fifty Query is
not updatable. However, I am only trying to update the Item table. I
see what your solution is doing, and I think it will work well, thank
you very much, but not sure why what I was doing was functionally different.

Can't tell from this, but I suspect that the Fifty query is not updatable for
some reason. You might get around this by using

Update Item
SET Item_id = "x"
WHERE Item.Id in
(SELECT Item_ID
FROM [Fifty special items to lowercase])

You can often use an non-updateable query in the where clause and still have a
working Update query.

UPDATE item INNER JOIN [Fifty special items to lowercase] ON
item.item_id = [Fifty special items to lowercase].item_id SET
item.active = "x";

Item is a table that I have full rights to. Item_id is unique.

[Fifty special items to lowercase] is a query that returns 50 unique
items from the item table that meet certain criteria.

There is a one to one matchup between the 50 items, and the Item table
which has many thousands of items.

Should be a simple update. Not sure why it fails. Any ideas?

 

[John Spencer]

The answer is Because Access WILL NOT let you do it the way you attempted.

In an update query (except for the where clause) all the tables or queries
used in the query must be updatable. I think that it makes poor sense but
this is the way it was implemented and that is what we are stuck with.

Another way to handle this would be to dump the Fifty query results to a
table and then use that table to update your Item table.

Perhaps you could enlighten me a little bit. Yes, the Fifty Query is not
updatable. However, I am only trying to update the Item table. I see
what your solution is doing, and I think it will work well, thank you very
much, but not sure why what I was doing was functionally different.

Can't tell from this, but I suspect that the Fifty query is not updatable
for
some reason. You might get around this by using

Update Item
SET Item_id = "x"
WHERE Item.Id in (SELECT Item_ID FROM [Fifty special items to lowercase])

You can often use an non-updateable query in the where clause and still
have a
working Update query.

UPDATE item INNER JOIN [Fifty special items to lowercase] ON
item.item_id = [Fifty special items to lowercase].item_id SET
item.active = "x";

Item is a table that I have full rights to. Item_id is unique.

[Fifty special items to lowercase] is a query that returns 50 unique
items from the item table that meet certain criteria.

There is a one to one matchup between the 50 items, and the Item table
which has many thousands of items.

Should be a simple update. Not sure why it fails. Any ideas?

 

[Phil]

Thanx. I may have to go the table route, as the other option seems to
take forever.

The answer is Because Access WILL NOT let you do it the way you attempted.

In an update query (except for the where clause) all the tables or queries
used in the query must be updatable. I think that it makes poor sense but
this is the way it was implemented and that is what we are stuck with.

Another way to handle this would be to dump the Fifty query results to a
table and then use that table to update your Item table.

Perhaps you could enlighten me a little bit. Yes, the Fifty Query is not
updatable. However, I am only trying to update the Item table. I see
what your solution is doing, and I think it will work well, thank you very
much, but not sure why what I was doing was functionally different.

Can't tell from this, but I suspect that the Fifty query is not updatable
for
some reason. You might get around this by using

Update Item
SET Item_id = "x"
WHERE Item.Id in (SELECT Item_ID FROM [Fifty special items to lowercase])

You can often use an non-updateable query in the where clause and still
have a
working Update query.

Phil wrote:


UPDATE item INNER JOIN [Fifty special items to lowercase] ON
item.item_id = [Fifty special items to lowercase].item_id SET
item.active = "x";

Item is a table that I have full rights to. Item_id is unique.

[Fifty special items to lowercase] is a query that returns 50 unique
items from the item table that meet certain criteria.

There is a one to one matchup between the 50 items, and the Item table
which has many thousands of items.

Should be a simple update. Not sure why it fails. Any ideas?