Update Query Problem

[Jonas]

Does anybody here know if it is possible to create an update query
such that the criteria is based on the table you intend to update
rather than the source of the data? If that is not possible, how can
I update a table with the information that I want in it? How can I
write a recordset or portions of a recordset to a table?

Your response is much appreciated.

 

[Michel Walsh]

Yes, you write the appropriate WHERE clause.

UPDATE table1
SET table1.price = 1.10 * table1.price
WHERE table1.price >= 50

which will increase the price by 10% for actual price >= 50


If data has to come from another table:


UPDATE table1 INNER JOIN table2
ON table1.itemID = table2.itemID
SET table1.price = table2.price



will change price of table1 for those from table2, for the matching itemID.



Hoping it may help,
Vanderghast, Access MVP

 

[KARL DEWEY]

Use a left join with Null as criteria.
Like this ---
UPDATE [Change Requests] LEFT JOIN [Change Requet-1] ON [Change
Requests].[Date open] = [Change Requet-1].[Date open] SET [Change
Requet-1].[Foreign] = [Primary_Key], [Change Requet-1].[Date open] = [Change
Requests].[Date open], [Change Requet-1].[Date close] = [Change
Requests].[Date close]
WHERE ((([Change Requet-1].[Date open]) Is Null));