getting syntax error in case statement in mysql query

Question

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

Taryn · Accepted Answer · 2013-01-17 10:15:36Z

1

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.

edited Jan 17, 2013 at 10:15

answered Jan 17, 2013 at 10:10

Taryn

249k57 gold badges375 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Strawberry Over a year ago

Pretty fast on the fiddle there bluefeet!

JN_newbie Over a year ago

Thanks bluefeet but what if "num" datatype is enum then what?

Taryn Over a year ago

@Java_NewBie I don't understand your question. Is this not working?

Dhruv Patel · Accepted Answer · 2013-01-17 10:09:55Z

0

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

answered Jan 17, 2013 at 10:09

Dhruv Patel

4041 gold badge5 silver badges16 bronze badges

Comments

sourcecode · Accepted Answer · 2013-01-17 10:11:09Z

0

you have an extra column in this select statement..

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

answered Jan 17, 2013 at 10:11

sourcecode

1,8122 gold badges16 silver badges17 bronze badges

Comments

Strawberry · Accepted Answer · 2013-01-17 10:16:28Z

0

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;

answered Jan 17, 2013 at 10:16

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Collectives™ on Stack Overflow

getting syntax error in case statement in mysql query

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related