CODEWITHSUNDEEP
mysqlsqlsql-insert
Anar Bayramov
Anar Bayramov

Reputation: 11594

How to define alias on case condition mysql

I am trying to move my one column to another one by changing the values but since the column names are not match (one is role_id and other one is challenge_id) I try to do create an alias as role_id it is not working. is my current query.

I try to do 

INSERT into role_user (role_id, user_id) 
            SELECT CASE 
                WHEN challenge_id=2 Then 6
                When challenge_id=3 then 7
                when challenge_id=4 then 8 
                when challenge_id=1 then 9
                END
                challenge_id as role_id , user_id
                FROM challenge_user

Upvotes: 1

Views: 548

Answers (1)

Mureinik
Mureinik

Reputation: 311853

You have a redundant "challenge id" between the the "END" and the "AS":

INSERT into role_user (role_id, user_id) 
            SELECT CASE 
                WHEN challenge_id=2 Then 6
                When challenge_id=3 then 7
                when challenge_id=4 then 8 
                when challenge_id=1 then 9
                END
                AS role_id,  -- Here!
                user_id
                FROM challenge_user

Moreover, since you aren't really using this alias for anything (just inserting it directly to another table), you could just drop it completely:

INSERT into role_user (role_id, user_id) 
            SELECT CASE 
                WHEN challenge_id=2 Then 6
                When challenge_id=3 then 7
                when challenge_id=4 then 8 
                when challenge_id=1 then 9
                END,
                user_id
                FROM challenge_user

Upvotes: 3

Related Questions