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
Post a Comment