Results 1 to 15 of 15
  1. #1
    Join Date
    May 2004
    Posts
    144

    Unanswered: update rows base on other rows

    hi
    i have table keep track of user points like this:

    id--user_id--points--points_prev
    1--10------15-----0
    2--10------18-----15
    3--10------20-----18

    as you maybe guess `points_prev` is points user earned in previous step.
    i wonder if there is a approach to update `points_prev` field with update query or a sp?
    tanX

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    If this is a one off operation then you can use the following, however, if this is something that is run frequently I would have a look at changing the data model to stored more conveniently.

    In order to run this as a one off take a look at user defined variables.

    Code:
    mysql> create table points (id int auto_increment primary key, userid int, points int, prev_points int);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert into points(userid, points) values (10,15);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into points(userid, points) values (10,18);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into points(userid, points) values (10,20);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> set @points = 0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @points, points, @points := points from points;
    +---------+--------+-------------------+
    | @points | points | @points := points |
    +---------+--------+-------------------+
    | 0       |     15 |                15 | 
    | 15      |     18 |                18 | 
    | 18      |     20 |                20 | 
    +---------+--------+-------------------+
    3 rows in set (0.00 sec)
    
    mysql> set @points = 0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> update points p join (select id, @points as prev_points, @points := points as newpoints from points order by userid, points) x using (id) set p.prev_points = x.prev_points;
    Query OK, 3 rows affected (0.00 sec)
    Rows matched: 3  Changed: 3  Warnings: 0
    
    mysql> select * from points;
    +----+--------+--------+-------------+
    | id | userid | points | prev_points |
    +----+--------+--------+-------------+
    |  1 |     10 |     15 |           0 | 
    |  2 |     10 |     18 |          15 | 
    |  3 |     10 |     20 |          18 | 
    +----+--------+--------+-------------+
    3 rows in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Removed!
    Because, already resolved.
    Last edited by tonkuma; 06-05-12 at 08:35.

  4. #4
    Join Date
    May 2004
    Posts
    144
    Tanx It-iss, this is frequent job, and i should do it frequently, do u suggest i should change db design? If so, what changes do you recomend?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bono56 View Post
    If so, what changes do you recomend?
    no changes to the database, but a simple change to your application

    look at the sample data in post #1 -- there are 3 rows

    when you go to insert the 4th row, calculate the "prev" data and then store it

    update query is not required -- not unless you go back and delete a row of history, in which case wholesale recalculation will be needed

    an even better strategy is not to store the previous value at all, but pull it out when you need it

    weird, i know, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2004
    Posts
    144
    TanX r937,
    But in this situation points field is updated by a UPDATE query, all in once, so i should update it later.
    And about storing prev data, maybe desribing the situation, make it more clear.
    I have a table keep track of points every user earn at every steps. I want to display a grid contains users with their last points, in this grid points change (points-points_prev) should display. So i think best way is always have points_prev in the record, i think pulling it out every time is not optimized solution for a table have 10000k record.
    Last edited by bono56; 06-06-12 at 18:39.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by bono56 View Post
    TanX r937,
    But in this situation points field is updated by a UPDATE query, all in once, so i should update it later.
    And about storing prev data, maybe desribing the situation, make it more clear.
    I have a table keep track of points every user earn at every steps. I want to display a grid contains users with their last points, in this grid points change (points-points_prev) should display. So i think best way is always have points_prev in the record, i think pulling it out every time is not optimized solution for a table have 10000k record.
    why would you think that?
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2004
    Posts
    144
    Because i need (points-point_prev) AS points_change in my all grid, and as i mentioned i think it's not optimized query to pull it out every time in big tables.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and again,. why would you think that without proving that your statement has validity?

    by storing the intermediate value you are creating an additional task.

    unless you have proof that its better to store the current sum to date then don't store it. valid reasons could be performance related, but you'd need to prove you had a performance problem first
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    bono, let's assume you will create your "previous" column

    please tell us what's wrong with this suggestion --
    look at the sample data in post #1 -- there are 3 rows

    when you go to insert the 4th row, calculate the "prev" data and then store it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2004
    Posts
    144
    tanX guys for your help,
    the table structure i mentioned in post#1, was kind simplified version, because i thought it's not neccessary to describe it with these details.
    this is the complete structure:
    suppose every user earns some points(points are aggregative) at every steps, & i keep track of users with their points in table like this
    Code:
    user--step--points--rank--rank_prev
    A--1--15--2--0
    B--1--18--1--0
    C--1--12--3--0
    
    A--2--32--1--2
    B--2--30--2--1
    C--2--25--3--3
    the procedure for insert these records in every steps are:
    first i calculate points that every user earned till that step, & insert the records, after that i update rank field base on `points` with update query like this:
    Code:
    SET @rank=0;
    UPDATE mytable
    SET rank = (@rank:=@rank+1),
    WHERE step=2
    ORDER BY points DESC
    in app interface i have grids that list all user with their points, in this grid i should display rank_change (current_rank-prev_rank) AS rank_change & i should have the ability to sort the grid base on `rank_change`.

    STEP 2:
    Code:
    user--step--points--rank--rank_prev--(rank_change)
    A--2--32--1--2--(+1)
    B--2--30--2--1--(-1)
    C--2--25--3--3--(0)

    so i thought best way is to keep `prev_rank` in every row. i don't know if it's the best approach or not?
    & r397, as you can see i update `rank` field with a update query, so i can't fill the field that way.
    Last edited by bono56; 06-10-12 at 16:29.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bono56 View Post
    don't know if it's the best approach or not?
    looks like it works fine

    congrats
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2004
    Posts
    144
    Quote Originally Posted by r937 View Post
    looks like it works fine

    congrats
    so how should i update this field?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bono56 View Post
    first i calculate points that every user earned till that step, & insert the records, after that i update rank field base on `points` with update query like this:
    Code:
    SET @rank=0;
    UPDATE mytable
    SET rnk = (@rank:=@rank+1),
    WHERE step=2
    ORDER BY points DESC
    what happened when you tested this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    May 2004
    Posts
    144
    Quote Originally Posted by r937 View Post
    what happened when you tested this?
    i'm using mentioned query for updating `rank` field & it's ok,
    now i need to update `rank_prev` field.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •