MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:emierzwa Date:February 10 2004 3:19pm
Subject:RE: A "current row updating" counter for a mass "Update" or similar?
View as plain text  
Sure, try this. I'm using 4.1.2 in case it matters.

set @n=0;
UPDATE Ranks_table
  SET Rank= (select @n:=@n+1)
ORDER BY Score DESC;

Ed

-----Original Message-----
From: admin@stripped [mailto:admin@stripped] 
Sent: Monday, February 09, 2004 6:37 PM
To: mysql@stripped
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/mysql?unsub=1

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