Mysql Question posted in
The official MySQL documentation can be found here.

Alter the table if table and column exists in MYSQL

I have a function & I’m checking whether the table and column exist or not. If exits, then I need to alter the table.

    delimiter $$

create function column_exists(ptable varchar(300), pcolumn varchar(100))
  returns CHAR
  reads sql data
begin
  declare result CHAR(1);
  select
    if(count(1)>=1,'Y','N')
  into
    result
  from
    information_schema.columns
  where
    table_schema = 'schema' and
    table_name  = ptable and
    column_name = pcolumn ;
  return result;
end $$

but I’m getting an error while using the function:

SET @column_exists := column_exists('table_name', 'column_name');

IF @column_exists = 'Y' THEN
    ALTER TABLE table_name MODIFY COLUMN column_name TIMESTAMP(3) DEFAULT '2019-01-01 00:00:00' NOT NULL;
ELSE
    SELECT 'Column does not exist';
END IF;

Error:

SQL Error [1064] [42000]: 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 @column_exists = 'Y' THEN
    ALTER TABLE table_name MODIFY COLUMN column_na' at line 3

Error position: line: 2

Please guide to resolve the issue. Thanks.

Tags:

2

Answers


  1. 0

    MySQL does not directly support the use of the IF statement outside of a stored procedure, function, or query.
    You have to include it in a stored procedure:

    delimiter $$
create procedure column_(IN aux varchar(1), ptable varchar(300), pcolumn varchar(100))
begin

    IF (aux = 'Y') THEN
        SET @sql_query = CONCAT('ALTER TABLE ', ptable, ' MODIFY COLUMN ', pcolumn, ' TIMESTAMP(3) DEFAULT ''2019-01-01 00:00:00'' NOT NULL');
        PREPARE stmt FROM @sql_query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    ELSE
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Column does not exist';
    END IF;
end $$

    and call it like this:

    SET @column_exists := column_exists('table_name', 'column_name');

call column_(@column_exists, 'table_name', 'column_name');
    Login or Signup to reply.
  2. 0

    I suggest use a Stored Procedure rather than a function.

    Include your schema name in the input parameters. You might have the same table and column name on two different schemas.

    DELIMITER &&

CREATE PROCEDURE ifColumnExist( IN tableName VARCHAR(255), IN columnName VARCHAR(255), IN columnDefinition VARCHAR(255), IN tableSchema varchar(50))
BEGIN

    IF ( SELECT COUNT(*) FROM information_schema.columns WHERE TABLE_SCHEMA = tableSchema AND TABLE_NAME = tableName AND COLUMN_NAME = columnName ) > 0  
     THEN
        SET @sql = CONCAT('ALTER TABLE ', tableName, ' MODIFY COLUMN ', columnName, ' ', columnDefinition);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
     ELSE
        -- If the column or table doesn't exist, signal an error
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Column or table does not exist';
    END IF;
END&&

DELIMITER ;

    Call it like the following

    call ifColumnExist('users','first_name','varchar(255) NOT NULL','crm_gesti');

    When successfully

    MySQL [crm_gesti]> call ifColumnExist('users','first_name','varchar(255) NOT NULL','crm_gesti');
Query OK, 0 rows affected (0.061 sec)

    When the column and table name doesn’t exist

     MySQL [crm_gesti]> call ifColumnExist('users','first_name','varchar(255) NOT NULL','asterisk');
ERROR 1644 (45000): Column or table does not exist
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search