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

This worked for me stackoverflow.com/a/689782/10204932

Deepam Gupta
– Deepam Gupta

2021-12-10 12:03:43 +00:00
Commented Dec 10, 2021 at 12:03 — Deepam Gupta
– Deepam Gupta, Commented Dec 10, 2021 at 12:03

GMB · Accepted Answer · 2019-09-19 23:14:22Z

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, 2019 at 23:14

answered Sep 19, 2019 at 23:12

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Deepam Gupta · Accepted Answer · 2021-12-10 12:26:39Z

4

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

answered Dec 10, 2021 at 12:26

Deepam Gupta

2,7522 gold badges33 silver badges37 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related