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

Question

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

I am using MySQL 5.7 (currently running this in MySQL Workbench) — Hooman Bahreini, Sep 19 '19 at 23:07
@VitalyBorisov: sorry that was a typo in question, I have updated — Hooman Bahreini, Sep 19 '19 at 23:08
Oracle Corporation sells (or gives away free) several databases, among them: their flagship Oracle RDBMS, MySQL, and Oracle RDB. `ALTER TABLE...ALTER COLUMN...` looks like Oracle RDB syntax. Oracle RDB was acquired by Oracle from Digital Equipment Corporation in 1994 during DEC's long, slow death agonies, before DEC was acquired by Compaq (which was subsequently acquired by HP). All of these different database products use different syntax for their DDL statements (CREATE TABLE, ALTER TABLE, etc) and have different capabilities. Ain't life **grand**? :-) — Bob Jarvis - Слава Україні, Sep 19 '19 at 23:28
This worked for me https://stackoverflow.com/a/689782/10204932 — Deepam Gupta, Dec 10 '21 at 12:03

GMB · Accepted Answer · 2019-09-19T23:14:22.037

4

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

edited Sep 19 '19 at 23:14

answered Sep 19 '19 at 23:12

GMB

216,147
25
84
135

thanks it worked. Is this version specific? Will Modify work in Other Version of MySQL? – Hooman Bahreini Sep 19 '19 at 23:14

score 4 · Answer 2 · answered Dec 10 '21 at 12:26

You can do this in three steps:

Add your new column (initially, let it have NULL values)

ALTER TABLE my_table ADD COLUMN new_col datatype NULL AFTER cur_col;

Update the table so that there are no NULL in our new column
```
UPDATE my_table SET new_col = 0 WHERE new_col IS NULL;
```

Modify our new column to NOT NULL

ALTER TABLE my_table MODIFY COLUMN new_col datatype NOT NULL;

Reference: StackOverflow- Altering a column: null to not null

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

2 Answers2