I have to alter my table adding a column that I have to fill with sequence of int numbers. Does anyone knows how can I fill this column? I tried this:
set @i = 1;
Update db.table set new_column=(@i := @i+1) where id>0;
But this just fill all lines with the number 2. =(
you can easy do so:
UPDATE db.table
CROSS JOIN ( SELECT @myid := 0) AS parameter
SET new_column = @myid := (@myid +1);
Try
Update your_table
join
(
select id, @i := @i+1 as rank
from your_table
cross join (select @i := 0) ia
where id > 0
order by id
) tmp on tmp.id = your_table.id
set your_table.new_column = tmp.rank
All this answers works for me. And I did another one
delimiter #
create procedure fill_column()
begin
declare v_max int unsigned default 83;-- number of registers that I have
declare v_counter int unsigned default 0;
declare a int default 0;
start transaction;
while v_counter < v_max do
update db.tb_table set new_column= (@a := @a+1) where id> 0;
set v_counter=v_counter+1;
end while;
commit;
end #
call fill_column();
Thanks guys =)
