Jump to content

Updating one table based on results of another table


cheeriomaster

Recommended Posts

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?

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...

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?

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.