I have a number of rows from a database which I want to insert into another database. Since there's quite a few rows I want to use the INSERT .. SELECT ... construct in mysql. The statement I try to run is as follows:
set @rank := 1; set @currpoints := 0; set @currgroupcount := 0;
SELECT id, @rank := if( @currpoints = points, @rank , @rank + @currgroupcount )
AS rank,
@currgroupcount := if( @currpoints = points, @currgroupcount +1, 1 ) , @currpoints :=
points
FROM characters
ORDER BY points DESC , name
LIMIT 0 , 30
The problem is that I can't insert the selected rows into the database when the result has more than two columns. Is there any way I can do these needed increments without returning the expressions as columns? If it isn't possible do you know what i can do as an alternative and still be able to get the performance gain that lies in using the INSERT .. SELECT ... construct?