Help on Update Query

[lagg]

I need help with an update query based on two tables.
The tables are linked with "stockid" field. The tables names are "sale items" and "products". There are multiple references of "stockid" in the "sale items" table, there is only one unique entry in the "products" field. I need to update the "costsale" field in "sale items" equal to the "averagecost" field in "products" tables.

Crude version of sql

update sale items set sale items.costsale=products.avecost
where sale items.stockid=products.stockid

 

[JerryKlmns]

update [sale items] inner join products on [sale items].stockid=products.stockid
set [sale items].costsale=products.avecost

 

[lagg]

Thank you for your help, the query prompts me to enter parameters however. I need to get it to copy the values of averagecost to sale items.costsale where stockid's are equal. Any ideas?

 

[lespaul]

If you just copied and pasted the above query, I would guess that you don't have a field name 'avecost' in your table, did you fix all the field and table names to exactly what you have in the tables?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lagg]

Yip I changed the query to suit my field names. Query prompts me to enter a parameter all the time?

 

[lespaul]

what parameter is it prompting for? what's the SQL of the query you are actually using?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design