CODEWITHSUNDEEP
mysqlsqlselect
Bearzi
Bearzi

Reputation: 558

MySQL Row counter in Update statement

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

Upvotes: 3

Views: 959

Answers (1)

ajreal
ajreal

Reputation: 47321

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;

Upvotes: 1

Related Questions