How do you use user variables in Mysql If clause?

Question

I want to alter a table and add a column to it if it doesn't already exist. So I populate a variable @row and if it's I want to alter the able as so.

SET @row = (SELECT count(*) FROM information_schema.COLUMNS WHERE TABLE_NAME='tblname' AND COLUMN_NAME='colname' and table_schema='dbname');   
IF(@row <1) THEN 
   ALTER TABLE tblname ADD colname INT;  
END IF;

But I am getting a syntax error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(@row <1) THEN ALTER TABLE tblname ADD colname INT' at line 1

What am I doing wrong?

juergen d · Accepted Answer · 2015-01-14 14:49:01Z

1

In MySQL you need a shell around flow statements like if.

Put a procedure around it and it will work.

answered Jan 14, 2015 at 14:49

juergen d

205k40 gold badges305 silver badges377 bronze badges

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

Comments

ravnur · Accepted Answer · 2015-01-14 15:25:37Z

0

You can use prepared statements

IF (@row <1) THEN 
  SET @lSQL = "ALTER TABLE tblname ADD colname INT";
  PREPARE stmt FROM @lSQL;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END IF;

Hope it helps.

answered Jan 14, 2015 at 15:25

ravnur

2,85223 silver badges28 bronze badges

Collectives™ on Stack Overflow

How do you use user variables in Mysql If clause?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related