CODEWITHSUNDEEP
mysqlsql
Dennis
Dennis

Reputation: 23

Changing the values of a table when running SQL query

I would like to change the values of a table column only for the SQL statement.

Lets say I have a table with the columns Name, Points, and Country. I'd like to change the names from what ever value there might be to 1, 2, 3, ... (e.g. Peter = 1, Mike = 2, Lisa = 3). The query should automatically assign values to the names as I don't know how many names there are for each country.

I did some research and only found this CASE solution. However, I would like to change all values of this column when running the SQL query without having to add each name to the CASE statement. 

SELECT Name, Points, Country
  CASE Name
    WHEN "Peter" THEN "1"
    ELSE Name
  END,
FROM ...

Note: the names are unique

Upvotes: 2

Views: 48

Answers (2)

Satish Shr
Satish Shr

Reputation: 446

SELECT Points, Country,
 Case when Name REGEXP '[a-zA-Z]' then  ROW_NUMBER() OVER ()
 ELSE name
 END AS Name
FROM table;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Hmmm . . . If you don't care which numbers are assigned to which name, you can use dense_rank():

select Name, Points, Country,
       dense_rank() over (order by Name) as name_int
from . . .

Upvotes: 1

Related Questions