another MySQL question

NoFear

I have another MySQL question for those of you more advanced than I am. I've been working on this for a couple hours now, but can't seem to get it to work properly:

Ok, let's say I have 4 different tables - rankings, asset_1, asset_2, and asset_3.
each of the three asset tables have 2 primary keys: user_id, and hour_1, and the rankings table has the primary key user_id, and another column called "value"

What I need to do is gather all the values from each of the "hour_1" columns in each of the three asset tables, and set the "value" column in the rankings table equal to the sum of those values specific to each user_id.

so, if the "hour_1" columns had the three values (2,3,4) for user_1, and (5,6,7) for user_2., I want the values 9 and 18 inserted into the "values" column for user_1 and user_2 in the rankings table.

How can I do this in one query for all the users???

NoFear

I've attached an example to clarify any questions you may have about my post. I'm really hoping somebody can help me out here. This has stumped me for the past 8 hours now...

tomhath

Try this (untested):

select a1.user_id,
 sum(a1.hour_1 + a2.hour_1 + a3.hour_1) as value
from asset_1 as a1
inner join asset_2 as a2
on a1.user_id = a2.user_id
inner join asset_3 as a3
on a1.user_id = a3.user_id
group by a1.user_id

NoFear

wow... I'll see if it works. Would you mind explaining the theory behind it for me as well, so I can know the details behind why you called it like that and what's actually going on?

Does the SUM(....) actually insert the result into "value" for future reference, or does it just return it like that for the length of the query??

If it doesn't actually place the number in the "value" column, it wouldn't quite work, because I need to reference the "value" in other queries. I'll try it and see what happens 🙂

NoFear

ok, it worked, and almsot completely how I wanted it to, however, the resulting value didn't store in the 'value' column, it was gone after the query was over.... any idea how to make this work??

I appreciate the help very, VERY much. Thanks again.

NoFear

I'm ever so close to getting it to work, but it's just not working. Here's what I have right now:

update user_rankings set influence =
select sum(a1.hour_1 + a2.hour_1 + a3.hour_1) as influence
from asset_1 as a1
inner join asset_2 as a2
on a1.user_id = a2.user_id
inner join asset_3 as a3
on a1.user_id = a3.user_id
group by a1.user_id;

Producers error:

ERROR 1064: You have an error in your SQL syntax near 'select sum(a1.hour_1 + a2.hour_1 + a3.hour_1) as influence
from asset_1 as a1
i' at line 2

However, I don't think MySQL supports the select statement in conjunction with the update syntax, so I don't know what to do.... 🙁

tomhath

You can't use row aggregate functions (like SUM) in UPDATE statements. SQL just doesn't allow it. :mad:

If you have mysql 4.x it could be done with a subselect, or you can store the results of the SELECT in a temporary table and update with a join, or use a scripting language like php, or use a cursor in SQL.

I'd probably go with a cursor loop. They're a little strange at first, but pretty simple once you've been through it a couple of times.