views:

164

answers:

3

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?

+1  A: 

You could hide the increment using something like:

SELECT IF(@currpoints := points, id, id)
chaos
+1  A: 
INSERT
INTO new_characters (id, name)
SELECT id, name
FROM (
  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
) m
Quassnoi
A: 

You can certainly achieve what you need by using cursors but there may be a performance hit.

Richard Dorman