The following MySQL statement is working fine, and it returns me the rownumber as row, of each result. But now, what I want to do, is setting the column pos with the value of "row", by using an update statement, since I don't want to loop thousands of records with single queries.
Any ideas?
SELECT @row := @row + 1 AS row, u.ID,u.pos
FROM user u, (SELECT @row := 0) r
WHERE u.year<=2010
ORDER BY u.pos ASC LIMIT 0,10000
There is a risk using user defined variables
In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected:
A more safe guard method will be
create table tmp_table
(
pos int(10) unsigned not null auto_increment,
user_id int(10) not null default 0,
primary key (pos)
);
insert into tmp_table
select null, u.ID
from user
where u.year<=2010
order by YOUR_ORDERING_DECISION
limit 0, 10000;
alter table tmp_table add index (user_id);
update user, tmp_table
set user.pos=tmp_table.pos
where user.id=tmp_table.user_id;
drop table tmp_table;
