CODEWITHSUNDEEP
mysql
Per Stilling
Per Stilling

Reputation: 876

How do I make an expression inside a select statement without it being returned as column?

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?

Upvotes: 3

Views: 268

Answers (3)

Richard Dorman
Richard Dorman

Reputation: 24120

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

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425643

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

Upvotes: 1

chaos
chaos

Reputation: 124325

You could hide the increment using something like:

SELECT IF(@currpoints := points, id, id)

Upvotes: 1

Related Questions