Updating one table based on results of another table

[cheeriomaster]

I have two tables set up like this:

 

Users

---------------

int user_id

decimal (13,2) cash

 

Values

---------------

int owner_id

decimal (8,2) value

 

What I would like to do is update Users.cash to equal User.cash + Values.value where Values.owner_id=Users.user_id.

 

Currently I have "UPDATE users,values SET users.cash=users.cash+values.value WHERE users.user_id=values.owner_id"

 

For example:

 

In Users we have 1 row with the following:

 

Users

---------------

row 1 - user_id 1234 | cash 100.00

 

In Values we have 3 rows with the following:

 

Values

---------------

row 1 - owner_id 1234 value 20.00

row 2 - owner_id 1234 value 10.00

row 3 - owner_id 4321 value 40.00

 

I would like my statement to update row 1 in Users to cash=130 or Users.row1.cash + Values.row1.value + Values.row2.value.

 

My current statement only appears to find one row in Values. What am I doing wrong?

[fallenangel1983]

Im unsure why it finds one row in values but it wont find all three as your third userID is different from the third one in your query. why not try doing seperate queries for each value in your Values table

[cheeriomaster]

I'm unsure why that query wasn't working either but I had an epiphany and though to try this:

 

UPDATE users SET cash=cash+(SELECT SUM(value) FROM values WHERE values.owner_id=users.user_id)

 

It worked perfectly! Just in case anyone else has a similar problem...

[cheeriomaster]

I take that back actually. My query is still:

 

UPDATE users SET cash=cash+(SELECT SUM(value) FROM values WHERE values.owner_id=users.user_id)

 

The strange thing is, if the second query of (SELECT SUM(value) FROM values WHERE values.owner_id=users.user_id) evaluates to greater than 0, the query works exactly as needed. When it evaluates to 0 though, it sets cash to 0 regardless of what cash was before. It would seem obvious that since I have cash=cash+ the second query, cash would remain the same if the second query resulted in 0. Any ideas on this one?