start mysql query with IF -


according mysql website should able start query if statement.

if search_condition statement_list [elseif search_condition statement_list] ... [else statement_list] end if 

but when try query

if (count(1) information_schema.table_constraints  table_schema = 'dbname' , table_name='tblname' , constraint_name = 'con_name') alter table table drop foreign key constraint_name; end if 

i syntax error saying have wrong syntax near "if" , mysql workbench highlights if saying syntax error, unexpected if.

i've tried begin if, , omitting both begin , end if error same.

you can't use if or while conditions out side statement unless enclosed in code block of begin - end. hence db engine threw error on statement.

for statement work, need stored procedure , changes statement well.

example:

delimiter //  drop procedure if exists drop_constraint //  create procedure drop_constraint(      in dbname varchar(64),      in tablename varchar(64),     in constraintname varchar(64) ) begin     declare cnt int default 0;      select count(1) cnt        information_schema.table_constraints                 table_schema = dbname         , table_name = tablename         , constraint_name = constraintname;      -- check if found     if ( cnt > 0 ) -- if found         -- now, execute alter statement         -- include alter table statement here     end if; end; //  delimiter ; 

using above procedure can check , drop constraint.

mysql> call drop_constraint( 'test', 'my_table', 'fk_name' ); 

Comments

Popular posts from this blog

Android layout hidden on keyboard show -

google app engine - 403 Forbidden POST - Flask WTForms -

c - Why would PK11_GenerateRandom() return an error -8023? -