_using mysql5
_I have a table "points" with (atleast) following fields
points_id (int, primary key), logged (datetime), sale_points(float),
available_points(float)
When a new row is inserted "available_points" is set to the value of
"sale_points"
I need to update the "available points" for each record starting from
oldest (from "logged" field) record
For example, i have the following records in points table
points_id, logged, sale_points, available_points
1 "2005-12-02 12:43:44" 100 100
2 "2005-12-02 12:44:02" 20 20
3 "2005-12-02 12:44:09" 1000 1000
4 "2005-12-02 12:44:47" 450 450
5 "2005-12-02 12:44:56" 10 10
Suppose i have a procedure "use_points" which accepts a float parameter
(points to use)
After executing "use_points" say:
use_points (620);
i expect it to use (update "available_points") 100 from 1
("points_id") leaving 0 "available_points", 20 from 2 leaving 0
"available_points", and 100 from 3 leaving 500 "available_points"
The resulting data in the points table should be
points_id, logged, sale_points, available_points
1 "2005-12-02 12:43:44" 100 0
2 "2005-12-02 12:44:02" 20 0
3 "2005-12-02 12:44:09" 1000 500
4 "2005-12-02 12:44:47" 450 450
5 "2005-12-02 12:44:56" 10 10
This is a how i intend to do the above
CREATE PROCEDURE use_points ( my_points FLOAT)
BEGIN
DECLARE cur_id INTEGER DEFAULT 0;
DECLARE cur_points FLOAT DEFAULT 0;
WHILE my_points > 0 DO
SELECT points_id, available_points INTO cur_id, cur_points
FROM points
WHERE logged = (SELECT MIN(logged) FROM points WHERE available_points > 0)
AND available_points > 0
ORDER BY points_id LIMIT 1;
IF my_points < cur_points THEN
UPDATE points SET available_points = cur_points - my_points WHERE
points_id = cur_id;
ELSE
UPDATE points SET available_points = 0 WHERE points_id = cur_id;
END IF;
SET my_points = my_points - cur_points;
END WHILE;
END
Is there a better and optimized way of doing this?
Thanks in advance
