CODEWITHSUNDEEP
mysqlsqlalter-table
skt
skt

Reputation: 589

Not able to add a column if it doesn't already exist?

I am trying to write a query to add a column in a MySql Table if it doesn't exist as below, but I am getting a syntax error as:

SQL Error (1064): 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 NOT EXISTS(SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHE' at line 1.

Query:

 IF NOT EXISTS(SELECT NULL 
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE  table_name = 'Devicebattery'
        AND table_schema = 'mat'
        AND column_name = 'WrittenToServerDatabaseTimestamp')  
THEN
  ALTER TABLE 'Devicebattery' ADD 'WrittenToServerDatabaseTimestamp'
    TIMESTAMP(3) NOT NULL DEFAULT '1970-01-01 00:00:01.000';
END IF;

Table details as below:-

CREATE TABLE Devicebattery (
    DeviceBatteryID INT(11) NOT NULL AUTO_INCREMENT,
    ByDevSessionId INT(11) NOT NULL,
    ByPatientId INT(11) NULL DEFAULT NULL,
    ByDeviceId INT(11) NULL DEFAULT NULL,
    Value FLOAT NULL DEFAULT NULL,
    SequenceId INT(11) NULL DEFAULT NULL,
    SyncStatus INT(11) NULL DEFAULT '0',
    Timestamp TIMESTAMP(3) NULL DEFAULT NULL,
    PRIMARY KEY (DeviceBatteryID),
    INDEX `ByDevSessionId` (ByDevSessionId),
    CONSTRAINT Devicebattery_ibfk_1 FOREIGN KEY (ByDevSessionId)
     REFERENCES devicesession (DeviceSessionID) ON UPDATE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=524994
;

Upvotes: 0

Views: 370

Answers (2)

Ritobroto Mukherjee
Ritobroto Mukherjee

Reputation: 111

TRY: 

DELIMITER $$
CREATE PROCEDURE Alter_Table()
BEGIN
  DECLARE _count INT;
  SET _count = (SELECT NULL 
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE  table_name = 'Devicebattery'
         AND table_schema = 'mat'
         AND column_name = 'WrittenToServerDatabaseTimestamp');
  IF _count = 0 THEN
    ALTER TABLE Devicebattery
        ADD COLUMN 'WrittenToServerDatabaseTimestamp' 
        TIMESTAMP(3) NOT NULL DEFAULT '1970-01-01 00:00:01.000';
  END IF;
END $$
DELIMITER ;

You can Try @Madhivanan's sollution also,if I've understand your need then both the procedures should work.

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

In MYSQL, IF ELSE logic should be part of either stored procedure or a user defined function. You should make this as a stored procedure if you are dynamically adding columns

Create procedure add_column
as
Begin
IF NOT EXISTS(SELECT NULL 
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE  table_name = 'Devicebattery'
        AND table_schema = 'mat'
        AND column_name = 'WrittenToServerDatabaseTimestamp')  
THEN
  ALTER TABLE 'Devicebattery' ADD 'WrittenToServerDatabaseTimestamp' 
  TIMESTAMP(3) NOT NULL DEFAULT '1970-01-01 00:00:01.000';
END IF;
end

Upvotes: 2

Related Questions