CODEWITHSUNDEEP
mysql
JN_newbie
JN_newbie

Reputation: 6062

getting syntax error in case statement in mysql query

I am getting a syntax error in case statement in mysql. 1064- you have an error in your sql statement.

insert into
abc_table(release_date,title,detail,num)  
select model1.release_date,model1.title,model1.detail,model1.num
CASE 
WHEN model1.num= 0 THEN 3
WHEN model1.num= 1 THEN 1
WHEN model1.num= 2 THEN 2
END AS model1.num
from def_table  model1

Is this syntax is correct in mysql

Upvotes: 1

Views: 2263

Answers (4)

Strawberry
Strawberry

Reputation: 33945

insert into abc_table(release_date,title,detail,num)  
select m.release_date
     , m.title
     , m.detail
     , CASE WHEN m.num = 0 THEN 3
            WHEN m.num= 1 THEN 1
            WHEN m.num= 2 THEN 2
        END num
  FROM def_table m;

Upvotes: 0

Taryn
Taryn

Reputation: 247680

You have two columns for model1.num:

insert into abc_table(release_date,title,detail,num)  
select model1.release_date,
   model1.title,
   model1.detail,
   model1.num -- <-- extra
   CASE 
      WHEN model1.num= 0 THEN 3
      WHEN model1.num= 1 THEN 1
      WHEN model1.num= 2 THEN 2
      END AS num
from def_table  model1

Your code should be:

insert into abc_table(release_date,title,detail,num)  
select model1.release_date,
   model1.title,
   model1.detail,
   CASE 
      WHEN model1.num= 0 THEN 3
      WHEN model1.num= 1 THEN 1
      WHEN model1.num= 2 THEN 2
      END AS num
from def_table  model1

Note: Sometimes, formatting your code will help find these errors. It can be difficult to see when all of the columns are in one row.

Upvotes: 1

sourcecode
sourcecode

Reputation: 1802

you have an extra column in this select statement..

select model1.release_date,model1.title,model1.detail,model1.num

Upvotes: 0

Dhruv Patel
Dhruv Patel

Reputation: 404

Try this,

you missed one comma

insert into
abc_table(release_date,title,detail,num)  
select model1.release_date,model1.title,model1.detail,
CASE 
WHEN model1.num= 0 THEN 3
WHEN model1.num= 1 THEN 1
WHEN model1.num= 2 THEN 2
END AS model1.num
from def_table  model1

Upvotes: 0

Related Questions