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

    Unanswered: UPDATE rows increasingly

    hi
    suppose in tbl like this:
    Code:
    CREATE TABLE `tbCities` (
      `id` int unsigned NOT NULL auto_increment,
      `title` varchar(50),
      `country_id` int ,
      `rnk` int,
      PRIMARY KEY  (`id`)
    );
    
    INSERT INTO `tbCities` (`title`, `country_id`) VALUES 
    ('New York', '1'),('Chicago', '1'),('Los Angeles', '1'),('Miami', '1'),
    ('London', '2'),('Liverpol', '2'),('Manchester', '2');
    the goal is to update `rnk` field increasingly base on field at the table.
    i have two scenarios, first update it base on a field (like id), i did it by this code:
    Code:
    SET @rank=0;
    UPDATE `tbCities` 
    SET rnk = (@rank:=@rank+1)
    ORDER BY id;
    at the second scenario i want to update rnk for every country separately, it means rnk=1...4 for country_id=1 & rnk=1...3 for country_id=2.
    & i dont want to use 2 query (1 for every country).
    is there a solution for such a thing?
    i was thinking about that and wonder i should reset @rank at some points, but how? i could not figure it out.
    Last edited by bono56; 09-30-12 at 19:37. Reason: typo

  2. #2
    Join Date
    Mar 2004
    Posts
    480
    see auto_increment in the manual, you can do the second scenario automatically with a myisam table type.

    the first scenario is unnecessary as you would already have that with the id field.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    It is possible using system variables. Try the following:

    Code:
    mysql> SET @rank = 0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SET @ctryid = 0;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> UPDATE tbCities
        -> INNER JOIN (SELECT id, IF(country_id <> @ctryid, @rank := 1, @rank := @rank + 1) as rank, @ctryid := country_id FROM tbCities ORDER BY id) AS ranks ON (ranks.id = tbCities.id)
        -> SET tbCities.rnk = ranks.rank;
    Query OK, 7 rows affected (0.03 sec)
    Rows matched: 7  Changed: 7  Warnings: 0
    
    mysql> SELECT * FROM tbCities;
    +----+-------------+------------+------+
    | id | title       | country_id | rnk  |
    +----+-------------+------------+------+
    |  1 | New York    |          1 |    1 | 
    |  2 | Chicago     |          1 |    2 | 
    |  3 | Los Angeles |          1 |    3 | 
    |  4 | Miami       |          1 |    4 | 
    |  5 | London      |          2 |    1 | 
    |  6 | Liverpol    |          2 |    2 | 
    |  7 | Manchester  |          2 |    3 | 
    +----+-------------+------------+------+
    7 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

  4. #4
    Join Date
    Mar 2004
    Posts
    480
    Quote Originally Posted by it-iss.com View Post
    It is possible using system variables.
    Much less work doing the primary key over the two columns with auto_increment though. The manual has an example.

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Of course provided you are free to use MyISAM tables only and that you accept that there might be gaps in the ranks if data is deleted and inserted in the table.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    you can mix and match table types but yes if transaction support was needed for the data it couldn't be done on that table type so it is a draw back.

    Gaps shouldn't matter really.

    Not knocking your solution if it came across that way.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what the heck? rank cities within country by id?

    since the id is an auto_increment, this means you have insert cities in pre-ranked sequence!

    what do you do if you have to add another city somewhere within the current ranking?

    something about this whole scenario smells bad...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2004
    Posts
    480
    Quote Originally Posted by r937 View Post
    something about this whole scenario smells bad...
    Bouvet Island bad?

  9. #9
    Join Date
    May 2004
    Posts
    144
    Quote Originally Posted by r937 View Post
    what the heck? rank cities within country by id?

    since the id is an auto_increment, this means you have insert cities in pre-ranked sequence!

    what do you do if you have to add another city somewhere within the current ranking?

    something about this whole scenario smells bad...
    off course the logic & table i mentioned here is just a sample, i don't want to sort by id. the update execute for records belongs to each user, base on calculated criteria.

  10. #10
    Join Date
    May 2004
    Posts
    144
    Quote Originally Posted by it-iss.com View Post
    It is possible using system variables. Try the following:
    Code:
    SET @rank = 0;
    SET @ctryid = 0;
    UPDATE tbCities
    INNER JOIN 
    (SELECT id, IF(country_id <> @ctryid, @rank := 1, @rank := @rank + 1) as rank, @ctryid := country_id FROM tbCities ORDER BY id) AS ranks ON (ranks.id = tbCities.id)
    SET tbCities.rnk = ranks.rank;
    it's working, but i wonder if it would possible without join, with more efficient way.
    tanX.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bono56 View Post
    off course the logic & table i mentioned here is just a sample, i don't want to sort by id.
    stinky penguin bad
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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