perform an update query for over 200 items

Question

In access I have an update function where I can update information in an inventory database 1 at a time. I enter the Item name and I able to update the cost as well as a date.

Is there a way to write an sql Query to perform an update for 200 unique items?

EDIT:

I have

|ITEM NAME|ITEM COST|DATE CHANGE|
|A        |$2.00    |1/1/1111   |
|B        |$3.50    |1/2/1111   |
|C        |$4.50    |1/3/1111   |

Let's say there are over 200 item names, I'd want to run a query to keep the item name but update the prices and date

|ITEM NAME|ITEM COST|DATE CHANGE|
|A        |$3.00    |1/4/1111   |
|B        |$1.50    |1/5/1111   |
|C        |$84.50   |1/6/1111   |

I feel the only way to do it is to just do one long update, but I don't know if there is something better to do that.

Possible duplicate of [Update multiple rows with different values in a single SQL query](http://stackoverflow.com/questions/11563869/update-multiple-rows-with-different-values-in-a-single-sql-query) — PyNEwbie, May 09 '16 at 15:01
You must have some data source for the new information - if it is in a 2nd table (or if you can get it into a table), you should be able to join the existing table to the update table on the item name field, then make it an update query to change the appropriate items. Any existing items not on the update table will stay as they were. — Don George, May 09 '16 at 20:44
that is correct, I'm only changing the costs and the dates, the item names stay the same. — walangala, May 09 '16 at 20:55

score 0 · Answer 1 · answered May 09 '16 at 15:21

I'm not sure if you mean that there's duplicate rows.. But I hope this could help by using the DISTINCT keyword from SQL. So if you were to write an update sql query it would be something like this:

UPDATE Accomodation
   SET AccomodationName = "YourChoice"
 WHERE AccomodationPrice = (SELECT DISTINCT AccomodationPrice 
                            FROM Accomodation)

For more information about distinct, you can see here.

score 0 · Answer 2 · answered May 09 '16 at 21:01

With a data table called Inventory and another table, Updates, with the same structure containing the 200 or so changes, the update would look like

UPDATE Inventory INNER JOIN Updates ON Inventory.[Item Name] = Updates.[Item Name] SET Inventory.[Item Cost] = [updates].[item cost], Inventory.[Date Change] = [updates].[date change];

perform an update query for over 200 items

2 Answers2