Search code examples
sqlmysqlalter-table

Adding a new NOT NULL column to an existing Table with data

I want to add a new NOT NULL column to an existing table which has data in MySQL 5.7. I have seen this question and I am using the solution suggested there.

I am adding the new column as NULL, populate data for the new column and then change the column from NOT NULL to NULL.

-- 1. add new column as null
ALTER TABLE `mytable` ADD COLUMN newCol BIT NULL AFTER curCol;

-- 2. populate default data for new column
SET sql_safe_updates = 0;
UPDATE `mytable` SET newCol = 0;
SET sql_safe_updates = 1;

-- 3. change the column to NOT NULL
ALTER TABLE `mytable` ALTER COLUMN newCol BIT NOT NULL;

But I am getting the following error on the last command:

Error Code: 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 'BIT NOT NULL:' at line 1

Solution

  • This piece of SQL is not valid in MySQL:

    ALTER TABLE `mytable` ALTER COLUMN newCol BIT NOT NULL;

    Instead, consider :

    ALTER TABLE `mytable` MODIFY newCol BIT NOT NULL;

    Reference : MySQL ALTER TABLE syntax