CODEWITHSUNDEEP
mysql
KristianMedK
KristianMedK

Reputation: 1841

start mysql query with IF

According to mysql website i should be able to start a query with an if statement.

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

but when i try this query 

if (count(1)
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_SCHEMA = 'dbname'
AND TABLE_NAME='tblname'
AND CONSTRAINT_NAME = 'con_name')
then
alter table table drop foreign key constraint_name;
end if

i get a syntax error saying i have the wrong syntax near "IF" and mysql workbench highlights the if saying syntax error, unexpected if.

i've tried with begin if, and omitting both begin and end if but the error is always the same.

Upvotes: 1

Views: 43

Answers (2)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

You can't use if or while conditions out side a statement unless they are enclosed in a code block of begin - end. Hence db engine threw an error on your statement.

For your statement to work, you need a stored procedure and some changes to the statement as 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) into cnt 
      from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
      where 
        table_schema = dbName
        and table_name = tableName
        and constraint_name = constraintName;

    -- now check if any found
    if ( cnt > 0 ) then -- if found some
        -- now, execute your alter statement
        -- include your alter table statement here
    end if;
end;
//

delimiter ;

Using the above procedure you can check and drop a constraint. 

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

Upvotes: 1

SergeS
SergeS

Reputation: 11779

You cannot, if both table have same structure (Or you put same structure instead of *) you can do this using unions this way 

select * from sometable WHERE (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME='tblname' AND CONSTRAINT_NAME = 'con_name') = 1
    union all
select * from anothertable WHERE (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME='tblname' AND CONSTRAINT_NAME = 'con_name') IS NULL

Alternatievly you can achieve this by using stored procedure (With almost same synbtax as you had wrote)

Upvotes: 0

Related Questions