Reputation: 79
I've got this structure table:
USER_ID | POINTS_BALANCE
10 | 180
10 | 20
10 | 100
10 | 120
How can I UPDATE points_balance field for example to 500 for all this users:
select user_id, sum(points_balance)
from `user_points_table`
group by user_id
having SUM(points_balance) >= 400 && SUM(points_balance) <= 499
Or can I INSERT a new ROW for user_id
that having sum(points_balance)
between 400 and 499?
EDIT:
For example:
select user_id, sum(points_balance)
from `user_points_table`
where user_id = 74
group by user_id
having SUM(points_balance) >= 400 && SUM(points_balance) <= 499
Result:
USER_ID | POINTS_BALANCE
74 | 434 <-- How can I update this result to 500?
Upvotes: 0
Views: 414
Reputation: 185
UPDATED
As I understand your question, you are looking for a way to set every users points to minimum of 500 by adding the difference. So, your solution might look like the following:
mysql> DELIMITER //;
-> CREATE PROCEDURE updateMinimum()
-> BEGIN
-> DECLARE u,s INT;
-> DECLARE cur CURSOR FOR
-> SELECT user_id, SUM(points) FROM user_points GROUP BY user_id;
-> OPEN cur;
-> read_loop: LOOP
-> FETCH cur INTO u,s;
-> IF s < 500 THEN
-> INSERT INTO user_points (user_id, points) VALUES (u, 500-s);
-> END IF;
-> END LOOP;
-> CLOSE cur;
-> END//
mysql> DELIMITER ;
mysql> CALL updateMinimum();
It will tell you there's No data
, but don't mind - it did the trick. Just use SELECT
to check it out:
SELECT user_id, SUM(points) FROM user_points GROUP BY user_id;
You may use CALL updateMinimum()
whenever you want, anytime.
Or if you don't need it anymore you can remove it by DROP PROCEDURE updateMinimum();
Upvotes: 0
Reputation: 62861
Since you want to add new a new row to the table, one option is to use insert into select
:
insert into user_points_table
select user_id, 500-sum(points_balance)
from `user_points_table`
where user_id = 74
group by user_id
having SUM(points_balance) >= 400 && SUM(points_balance) <= 499;
Upvotes: 1
Reputation: 37129
You cannot update the information you derived. If you are looking to display a different number from what you derived, you can do this:
select
user_id,
case
when totals < 500 then 500
else totals
end as totals
from (
select user_id, sum(points_balance) as totals
from test
-- where user_id = 10
group by user_id
having totals between 400 and 499
) t
-- where user_id = 10;
+---------+--------+
| user_id | totals |
+---------+--------+
| 10 | 500 |
+---------+--------+
If the result of
select user_id, sum(points_balance) as totals
from test
-- where user_id = 10
group by user_id
having totals between 400 and 499;
is
+---------+--------+
| user_id | totals |
+---------+--------+
| 10 | 420 |
+---------+--------+
and you want to bump this up to 500, you know that the difference between 500 and 420 is 80. So, insert a new record:
insert into test (user_id, points_balance) values (10, 80);
or you can update one of the records to:
update test set points_balance = points_balance + 80
where user_id = 10 and points_balance = 120;
The question follows - which of the many records for 10 will you update? I hope you have an id to uniquely identify each row. If you have that, update the row with lowest id. If you don't have a field that uniquely identifies each row, this would be a good time to create one such field.
In order to do this all in one shot, you would build a stored procedure or write a script in PHP/Python/whatever language you feel comfortable.
Upvotes: 0