MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:<admin Date:February 10 2004 1:36am
Subject:A "current row updating" counter for a mass "Update" or similar?
View as plain text  
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.


Thread
A "current row updating" counter for a mass "Update" or similar?admin10 Feb
  • Re: A "current row updating" counter for a mass "Update" or similar?Don Read10 Feb
RE: A "current row updating" counter for a mass "Update" or similar?emierzwa10 Feb
  • Re: A "current row updating" counter for a mass "Update" or similar?admin10 Feb