CODEWITHSUNDEEP
mysqlsqlmysql5
Suresh Atta
Suresh Atta

Reputation: 121998

Checking column exists with If else condition in sql

I'm trying to check weather thw column exists or not

IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_consumer' AND COLUMN_NAME='businness_id' > 0 ) THEN 
 PRINT 'test'

Whats wrong with above sql? getting error as

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 (Select COUNT(*) From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'tb_consu' at line 1

New to SQL. Thanks for any help,

Version of MySql is 5.X

Upvotes: 1

Views: 3787

Answers (4)

Matt
Matt

Reputation: 15061

SQL command is not a Boolean function. Therefore you need to check the value.

Like this:

IF (SELECT COUNT(*) 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'tb_consumer' AND COLUMN_NAME='businness_id') >= 0 
THEN PRINT 'test'

Upvotes: 1

Rhythem Aggarwal
Rhythem Aggarwal

Reputation: 356

i would suggest using CASE statement in MYSQL

SELECT CASE 
    WHEN COLUMN_NAME = 'businness_id' then 'TEST'
    END
    from FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'tb_consumer'

Case statements are faster on smaller databases

Upvotes: 1

Olli
Olli

Reputation: 1738

If you try to use this as part of a stored procedure, you might take a look at the if statement in the mysql documentation

if you do this as part of a normal query, you can write it like

SELECT IF (COUNT(*) > 0, 'TEST, 'TEST FAILED') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_consumer' AND COLUMN_NAME='businness_id'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can fix the query by moving the closing paren:

IF (SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'tb_consumer' AND COLUMN_NAME = 'businness_id'
   ) > 0 THEN 
 PRINT 'test'

A better way to write the condition is using exists:

IF EXISTS (SELECT 1
           FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = 'tb_consumer' AND COLUMN_NAME = 'businness_id'
          ) THEN

Upvotes: 4

Related Questions