update multiple rows + adjusting values

[sevenfive]

if i have a list of products with a value containing the number of items in stock held in my mysql db...

ie;

fluffy hat = 6
smelly coat = 8
fancy pants = 4

how could i make an update query that could adjust these values, either adding or subtracting from them, to obtain a new value.. but across multiple rows...

so for example i've sold a bunch of stuff and bought some new stock...

fluffy hat = 6 (-1)
smelly coat = 8 (-3)
fancy pants = 4 (+5)

any ideas??

[The_Chancer]

Use a dynamically created update query, that will run over and over until all updates have been completed ?

[sevenfive]

what so make a loop that runs the query for each entry? that sound like pretty taxing for the db?

it could be 1000 individual requests?

is there anyway to do it one hit?

[The_Chancer]

AFAIK I am afraid not....

But if it is only chamging values - it should be done in seconds..

Doing it over multiple tables would have been worse..

[sevenfive]

ok so if i get a count of the number of rows... then start the loop and increment a counter var until i've reached the total of rows would that work?

or is there a simpler way?

[The_Chancer]

As far as I can see yes....

Have you thought of a table dump though... migt work a little better... - using a CSV file.. (not sure if that would be easier for you though)

Would one person be doing the changes direct to the database ?