Sure, try this. I'm using 4.1.2 in case it matters. set @n=0; UPDATE Ranks_table SET Rank= (select @n:[EMAIL PROTECTED]) ORDER BY Score DESC;
Ed -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, February 09, 2004 6:37 PM To: [EMAIL PROTECTED] Subject: A "current row updating" counter for a mass "Update" or similar? Hi all, I'll just get straight to the problem (simplified): Say I have the following table/columns: ID, Score, Rank 1, 20, 0 2, 50, 0 3, 10, 0 4, 5, 0 5, 97, 0 Is it possible, in a single query (using sub-queries if necessary), to give all of these "ID's" the correct rank... ie if such a thing existed it might look like: UPDATE table_name SET rank=(current_row_updating+1) ORDER BY score DESC; Which would, you'd hope, make the table data then read: ID, Score, Rank 1, 20, 3 2, 50, 2 3, 10, 4 4, 5, 5 5, 97, 1 But I could find no nice looking commands that would give me this (perhaps that is not possible due to the nature of the database structure and the nature of updating? Though there must be some counter for it to report back with how many rows matched, how many were updated etc.). I also tried with a sub-query which the equivalent for the above example might look something like: UPDATE table_name SET rank=(SELECT COUNT(*) FROM table_name AS t2 WHERE t2.score>table_name.score); But couldn't seem to make it work - I received MySQL error messages with my attempts, I think possibly because I am trying to use the table I am trying to update to also pull select data from during the update - and it'd need a little adding to it anyway - if scores were equal, it'd want to default to lower ID number "wins" the higher rank. I can't seem to find any obvious nice way of doing this, other than the poor method of looping/iterating through with an "Ordered" select, and then sending individual "UPDATES" for each and every row received by the select (which in the above simplified example would be 6 query calls - 1 to get the select, followed by 5 seperate updates sent by PHP using PHP to know what "rank #" we were currently on). Thanks in advance, I hope somebody can point me to a possible solution of getting all of this with a single query as I have attempted, I'd appreciate any help on this matter. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
