Update Query from Select Query Help

[stephenk.smith]

I have a fairly straightforward question that I hope someone here has
encountered before. Here goes...

I have 2 tables:

Table 1
part_no - number
price - number

Table 2
part_no - number
new_price - number

I have 2 queries: 1st is used to calculate a new price, second is used
to update table2's price with new price from 1st query.

Query 1 - Selects part_no and price from table1

Query 2 - Updates new_price based on fields from Query 1
(ie. UPDATE table 2 SET new_price = price WHERE table1.part_no =
table2.part_no)

For some reason I cannot get the update query to run successfully. I
get the errors "Must use updatable query" or "Type mismatch in
expression" based on how I structure query 2.

I heard that it might not be possible to do an update query in this
fashion (from a select query). Any suggestions would be great. Thanks!

 

[Allen Browne]

Do both tables have the part_no marked as primary key? If not, that will
make a difference to the updatability of the query.

Once they both have the primary key, join the 2 tables on part_no in query
design. That should give you the right results, and will be more efficient
that the WHERE clause.

For other general clues, see:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

 

[stephenk.smith]

It seems like this should be more straightforward than it is. Can you
not update table values using an update query that references a select
query?

Right now I have this simple setup

table 1
part_no price
1 2
2 3

table 2
part_no price
1 0
2 0

Query 1: Select part_no, price from table 1
After running query 1 it looks like this

Query 1
part_no price
1 2
2 3

Query 2:

UPDATE table 2
SET table2.price = query1.price
WHERE table2.part_no = query1.part_no

When I run this update query it brings up an input box for
query1.price...

 

[Allen Browne]

If the source is not updatable, the UPDATE won't work.

A nasty workaround is to use a DLookup() if there are only a few values, or
if this is just a quick'n'dirty solution.