this is alittle complex

[timtimtimma]

Is it possible to select X amount of rows from a mysql database, organize them via a particular field from largest to smallest, and then UPDATE that field based on the order the rows were pulled in ONE query?

the reason for wanting to put all of this into one query because the only other way i know how to achieve what i want to achieve is to update the rows in one query and then select the rows in a seperate query sorting them from smallest to largest and run a while loop with an update query that updates the rows one by one, the only problem with this is I plan on doing THOUSANDS of rows in a single update every 24 hours, and im pretty sure a few hundred thousand queries would probably crash the server, or slow it extremly at the very least.

So, any suggests or help here would be greatly appreciated

[fenway]

I don't see why not -- you can have a select subquery ordered by whatever you want, with a limit clause, and then an update as the outer query.

[timtimtimma]

Ok, I have done alittle research, and let me rephrase what it is I am trying to do...

[code]
UPDATE table
SET rank=AUTO_INCREMENT_STARTING_WITH_1_HERE
ORDER BY rating
[/code]

I am wanting a table to be ordered a particular way a then a field given a number based on the order it was recieved starting with 1. So if it was ordered first, it would get rank=1, if it was ordered second, it would get rank=2, and so on and so fourth.

A few people have told me its not possible, but there has got to be a way to do it :(

[fenway]

First, there doesn't have to be a way to do it in a single query; a temporary table is always an option. Second, you can determine what you call "rank" simply by using a server variable counter, which in principle, you should be able to use to update any column. So I still say it's possible.

[wickning1]

I've written scripts that performed well over 3 million updates without any kind of pause. MySQL had no problem with them, and was serving up web pages to the normal users too.

But I think the best way to solve your problem is with a stored procedure (if you're using MySQL 5):
[code]CREATE PROCEDURE updateRatings
BEGIN
   DECLARE done, currid INT DEFAULT 0;
   DECLARE rankCount INT DEFAULT 1;
   DECLARE myRatings CURSOR FOR SELECT id FROM table ORDER BY rating;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
   OPEN myRatings;
   REPEAT
      FETCH myRatings INTO currid;
      IF currid IS NOT NULL THEN
         UPDATE table SET rank=rankCount WHERE id=currid;
         rankCount = rankCount + 1;
      END IF;
   UNTIL done END REPEAT;
   CLOSE myRatings;
END[/code]

If you're still on MySQL 4, just do the while loop on your end, it won't bring the server down.

[timtimtimma]

I wish I had mysql 5 but i dont..

And, You are aware that im actually going to be putting a query WITHIN the while loop, right?

Ive done it before, and it slows down the server terribly :(((( I am just trying to avoid severe server stress that ive been part of before ;-(

[!--quoteo(post=347287:date=Feb 19 2006, 01:03 AM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Feb 19 2006, 01:03 AM) [snapback]347287[/snapback][/div][div class=\'quotemain\'][!--quotec--]
First, there doesn't have to be a way to do it in a single query; a temporary table is always an option. Second, you can determine what you call "rank" simply by using a server variable counter, which in principle, you should be able to use to update any column. So I still say it's possible.
[/quote]

How would I go about getting this mysql server variable counter?

[shoz]

I WOULD NOT suggest doing it this way, but using MYSQL 4.1 I was able to get the desired result using one query with the following.


To create table `users`
[code]
CREATE TABLE `users` (
  `name_id` int(11) NOT NULL auto_increment,
  `name` varchar(60) default NULL,
   `rank` int(11) default NULL,
  PRIMARY KEY  (`name_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `users` VALUES (1,'john',0),(2,'steve',0),(3,'smith',0),(4,'fred',0);
[/code]

The query we want to rank by
[code]
SELECT * FROM users ORDER BY name DESC
[/code]

query to update the rank
[code]
UPDATE
users AS n1
INNER JOIN
    (
    SELECT
    @cc:=1, sn2.name_id, sn2.name,
        (
        SELECT
        (SUM(IF(sn1.name_id=sn2.name_id, @cc:=0, @cc))+1)
        FROM
            (
            SELECT
            *
            FROM
            users
            ORDER BY
            name DESC
            ) AS sn1
        ) AS rank
    FROM
    users AS sn2
    ORDER BY
    name
    DESC
    ) AS n2
ON n1.name_id=n2.name_id
SET
n1.rank=n2.rank;
[/code]
Using the temporary table option suggested by fenway you can create the temporary table with an auto_increment column and create the update query based on that.
[code]
CREATE TEMPORARY TABLE users_tmp (rank INT AUTO_INCREMENT, name_id INT, PRIMARY KEY(rank));

INSERT INTO users_tmp SELECT '', id FROM users ORDER BY name DESC;

UPDATE users n1 INNER JOIN users_tmp n2 ON n1.id=n2.name_id SET n1.rank=n2.rank;
[/code]
You should [a href=\"http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html\" target=\"_blank\"]lock[/a] the table before running the queries or find some other way to ensure that the table doesn't change during the queries. If you'd like to be able to continue "Selecting" from the table during the update, consider using [a href=\"http://www.innodb.com/index.php\" target=\"_blank\"]InnoDB[/a] tables.

Using php you can create the update using something similar to the following
[code]
$query = 'SELECT * FROM users ORDER BY name DESC ';
$update_query = 'UPDATE users SET rank = CASE name_id ';
for ($i = 1;$row = mysql_etc($result); $i++)
{
   $update_query .= "WHEN {$row['name_id']} THEN  $i ";
}
$update_query .= 'END ';
mysql_query($update_query);
[/code]
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html\" target=\"_blank\"]Control Flow Functions[/a]
Again, lock the table when doing the queries.

[timtimtimma]

[!--quoteo(post=347468:date=Feb 19 2006, 01:43 PM:name=shoz)--][div class=\'quotetop\']QUOTE(shoz @ Feb 19 2006, 01:43 PM) [snapback]347468[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I WOULD NOT suggest doing it this way, but using MYSQL 4.1 I was able to get the desired result using one query with the following.
To create table `users`
[code]
CREATE TABLE `users` (
  `name_id` int(11) NOT NULL auto_increment,
  `name` varchar(60) default NULL,
   `rank` int(11) default NULL,
  PRIMARY KEY  (`name_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `users` VALUES (1,'john',0),(2,'steve',0),(3,'smith',0),(4,'fred',0);
[/code]

The query we want to rank by
[code]
SELECT * FROM users ORDER BY name DESC
[/code]

query to update the rank
[code]
UPDATE
users AS n1
INNER JOIN
    (
    SELECT
    @cc:=1, sn2.name_id, sn2.name,
        (
        SELECT
        (SUM(IF(sn1.name_id=sn2.name_id, @cc:=0, @cc))+1)
        FROM
            (
            SELECT
            *
            FROM
            users
            ORDER BY
            name DESC
            ) AS sn1
        ) AS rank
    FROM
    users AS sn2
    ORDER BY
    name
    DESC
    ) AS n2
ON n1.name_id=n2.name_id
SET
n1.rank=n2.rank;
[/code]
Using the temporary table option suggested by fenway you can create the temporary table with an auto_increment column and create the update query based on that.
[code]
CREATE TEMPORARY TABLE users_tmp (rank INT AUTO_INCREMENT, name_id INT, PRIMARY KEY(rank));

INSERT INTO users_tmp SELECT '', id FROM users ORDER BY name DESC;

UPDATE users n1 INNER JOIN users_tmp n2 ON n1.id=n2.name_id SET n1.rank=n2.rank;
[/code]
You should [a href=\"http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html\" target=\"_blank\"]lock[/a] the table before running the queries or find some other way to ensure that the table doesn't change during the queries. If you'd like to be able to continue "Selecting" from the table during the update, consider using [a href=\"http://www.innodb.com/index.php\" target=\"_blank\"]InnoDB[/a] tables.

Using php you can create the update using something similar to the following
[code]
$query = 'SELECT * FROM users ORDER BY name DESC ';
$update_query = 'UPDATE users SET rank = CASE name_id ';
for ($i = 1;$row = mysql_etc($result); $i++)
{
   $update_query .= "WHEN {$row['name_id']} THEN  $i ";
}
$update_query .= 'END ';
mysql_query($update_query);
[/code]
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html\" target=\"_blank\"]Control Flow Functions[/a]
Again, lock the table when doing the queries.
[/quote]


why would you NOT want to do this?

[fenway]

My $0.02 -- go the temporary table route. It's probably the least intensive, and the easiest to understand and maintain.

[shoz]

[quote]
why would you NOT want to do this?
[/quote]
I was referring to the first query with the SUBSELECTs. I'm guessing that on a large table it would be very slow and the way user defined variables are handled in a query like this is tricky to understand(took me a while to figure out a way to get it working) and may change from version to version. The other methods should be fine.

I can't comment on whether or not you should do what you're asking for help to do, because I don't know why you're doing it.

EDIT: added comment about variables.

[shoz]

This UPDATE statement is simpler than the original one I posted. No variables etc. Probably faster as well but still I'd think, slow.
[code]
UPDATE
users AS n1
INNER JOIN
    (
    SELECT
    name_id,
        (
        SELECT
        COUNT(*)
        FROM
        users AS n3
        WHERE
        n3.name >= n2.name
        ) AS rank
    FROM
    users AS n2
    ) AS n4
ON
n1.name_id=n4.name_id
SET n1.rank = n4.rank
[/code]
There may even be a simpler way.

[timtimtimma]

would a temporary table last long enough for me to perform a query that dumps/updates the data back into the original table? A friend is helping me figure out the temporary table thing but the only way he knows how to update the original table based on the temp table is to perform a query within a while loop..And I do nt want to travel down this path :(

[shoz]

The temporary table should last until your session ends(the connection closes). You only have to perform the queries in the order shown previously.

[code]
mysql_query(create temptable table_tmp) etc
mysql_query(insert into table_tmp) etc
mysql_query(update orig_table) etc
[/code]
Btw, you should NOT use a persistent connection(mysql_pconnect) when using the temporary table option. Although, for a script being run once a day it shouldn't matter.

The following is a rewrite of the queries, so that it's easier to follow
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]CREATE TEMPORARY TABLE tablename_tmp (rank INT AUTO_INCREMENT, name_of_id_field INT, PRIMARY KEY(rank)); [!--sql2--][/div][!--sql3--]
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']INSERT[/span] [color=green]INTO[/color] [color=orange]tablename_tmp[/color] [span style=\'color:blue;font-weight:bold\']SELECT[/span] '', name_of_id_field [color=green]FROM[/color] [color=orange]tablename[/color] [color=green]ORDER BY[/color] field_to_rank_by [!--sql2--][/div][!--sql3--]
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']UPDATE[/span] tablename [color=green]AS[/color] t1 INNER [color=green]JOIN[/color] tablename_tmp t2 ON t1.name_of_id_field[color=orange]=[/color]t2.name_of_id_field SET t1.field_to_store_rank_[color=green]in[/color] [color=orange]=[/color] t2.rank; [!--sql2--][/div][!--sql3--]
"tablename" is the name of the table you're working with.

If you have any problems post what you've tried and also post the create statement for your table
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]SHOW CREATE TABLE tablename [!--sql2--][/div][!--sql3--]
Explaining which field should store the rank and which field the rank should be based on

[timtimtimma]

I got it to work in phpmyadmin with the following code! :D

[code]
CREATE TEMPORARY TABLE new_ladder
(
new_rank integer auto_increment primary key,
id integer
)
SELECT id
FROM vod_ladder
ORDER BY rating
DESC;

UPDATE vod_ladder AS vdl, new_ladder AS nvdl
SET vdl.rank = nvdl.new_rank
WHERE (vdl.id=nvdl.id)
[/code]

ive tried executing the code like this:

[code]
    $exe_sql = "UPDATE ".$pre."ladder
                SET rating=c_rating, wins=c_wins, losses=c_losses
                WHERE (rating != c_rating)
                OR (wins != c_wins)
                OR (losses != c_losses)";
    $sql->sql_query();
    
    $exe_sql = "CREATE TEMPORARY TABLE new_ladder
                (
                    new_rank INTEGER AUTO_INCREMENT PRIMARY KEY,
                    id INTEGER
                )
                SELECT id
                FROM vod_ladder
                ORDER BY rating
                DESC";
    $sql->sql_query();
    $exe_sql = "UPDATE vod_ladder AS vdl, new_ladder AS nvdl
                SET vdl.rank = nvdl.new_rank
                WHERE (vdl.id = nvdl.id)";
    $sql->sql_query();
[/code]

But when i run it I get the error:

There appears to be an error. Below the information is provided.
Code: 1044
Message: Access denied for user 't3_user'@'localhost' to database 't3_DB'

Any ideas? 0_o

[shoz]

First let me say that I like the combination that you used for defining the columns and using the SELECT.

I've usually either done a "CREATE TABLE tablename SELECT etc" or done the definition and then an insert, when using data from another table. The behaviour is slightly different between 4.0 and 4.1 though.

To the error. I'd assume that you need to GRANT access to "t3_user@localhost" ON the database t3_DB.
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]GRANT ALL ON t3_DB.* TO t3_user@localhost;
[!--sql2--][/div][!--sql3--]
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/privilege-system.html\" target=\"_blank\"]Privilege System[/a]
[a href=\"http://dev.mysql.com/doc/refman/4.1/en/grant.html\" target=\"_blank\"]Grant Syntax[/a]

[timtimtimma]

Thank you everyone I have got everythin to work exactly how I hoped it would, and now I have set the script up as a cron job and its ran once every 24 hours :)

Big thank you to everyone who helped.