Update Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I create an update query that updates a table from a query? I have a
query that I need one of the fields to update into a table. Can this be done?

In my query I have a field "Expr1" that I need to put into a
tblRawDiameterPricing.BCPrice
 
Hi,



UPDATE tablename SET fieldName = constant


will update all the rows (since there is no WHERE clause) with the constant
value


UPDATE prices INNER JOIN newPrices ON prices.itemID = newPrices.ItemID
SET prices.UnitPrice = newPrices.UnitPrice


will update the rows in table prices, their column UnitPrice, by the
unitPrice in table newPrices, for the matching ItemID.


Hoping it may help,
Vanderghast, Access MVP
 
How do I create an update query that updates a table from a query? I have a
query that I need one of the fields to update into a table. Can this be done?

In my query I have a field "Expr1" that I need to put into a
tblRawDiameterPricing.BCPrice
Click to expand...

How do you determine WHICH record in tblRawDiameterPricing should be
updated? You'll need to join your query to the table (by the
appropriate join fields, which obviously I don't know); change it to
an Update query; and put

[yourqueryname].[Expr1]

on the Update To line under BCPrice.

John W. Vinson[MVP]
 
The way it would know which records to update woule be based on the [Size] &
[MaterialType] fields. If they are both equal then have it update the
[BCPrice] field.
How would I write this into the update query?

John Vinson said:
How do I create an update query that updates a table from a query? I have a
query that I need one of the fields to update into a table. Can this be done?

In my query I have a field "Expr1" that I need to put into a
tblRawDiameterPricing.BCPrice
Click to expand...

How do you determine WHICH record in tblRawDiameterPricing should be
updated? You'll need to join your query to the table (by the
appropriate join fields, which obviously I don't know); change it to
an Update query; and put

[yourqueryname].[Expr1]

on the Update To line under BCPrice.

John W. Vinson[MVP]
Click to expand...
 
The way it would know which records to update woule be based on the [Size] &
[MaterialType] fields. If they are both equal then have it update the
[BCPrice] field.
How would I write this into the update query?
Click to expand...

In the query grid, add the table and the query. Drag the Size field
from the query to the Size field from the table, and the same with the
MaterialType.

Then put

[yourqueryname].[BCPrice]

on the Update To line under the table's BCPrice field. The brackets
are required - otherwise it will try to update to the text string you
type!

The query will ONLY work if you have a unique Index on thee
combination of Size and MaterialType in the table.

The SQL of the query (assuming that your query is named yourqueryname
and the table is named yourtablename, adjust as needed):

UPDATE yourtablename INNER JOIN yourqueryname
ON yourtablename.[Size] = yourqueryname.[Size]
AND yourtablename.[MaterialType] = yourqueryname.[MaterialType]
SET [yourtablename].[BCPrice] = [yourqueryname].[BCPrice];


John W. Vinson[MVP]
 
Back
Top