I’m working on modifying a database table structure and need to insert several new columns right after an existing column named surname. When I try to add multiple columns at once using the AFTER clause, I keep getting syntax errors.
Here’s what I attempted:
ALTER TABLE `employees` ADD COLUMN
(
`total` int(5) NOT NULL,
`notes` varchar(20) NOT NULL,
`active` tinyint(1) NOT NULL
)
AFTER `surname`;
This gives me a syntax error message:
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 ‘) AFTER surname’ at line 7
What’s the correct way to position multiple new columns after a specific existing column in MySQL? Is there a proper syntax for using AFTER with multiple column additions, or do I need a different approach?
Your syntax won’t work in MySQL. You can’t use AFTER when adding multiple columns in one statement - MySQL doesn’t allow it. You’ll need separate ALTER TABLE statements for each column:
ALTER TABLE `employees` ADD COLUMN `total` int(5) NOT NULL AFTER `surname`;
ALTER TABLE `employees` ADD COLUMN `notes` varchar(20) NOT NULL AFTER `total`;
ALTER TABLE `employees` ADD COLUMN `active` tinyint(1) NOT NULL AFTER `notes`;
This way each column gets placed exactly where you want it. First one goes after surname, second after the first new column, and so on. Running them separately gives you complete control over the order.
MySQL doesn’t allow you to use the AFTER clause when adding multiple columns in a single statement. A practical approach is to add all the columns first without using AFTER, and then modify their positions with separate ALTER statements. This method is cleaner compared to just placing them at the end, as it gives you more control. From my experience with large databases, handling column placements one by one allows for easier rollbacks and monitoring of changes, especially in tables with significant amounts of data.
The manual approach works but gets tedious with lots of schema changes across environments.
I hit this same issue managing database migrations for different staging setups. Instead of running individual ALTER statements each time, I automated it.
Set up a workflow that takes your column definitions and generates the ALTER statements automatically. Define what columns you want and where - automation handles the syntax and execution order.
For your case, specify the three columns and target position, and it generates:
ALTER TABLE employees ADD COLUMN total int(5) NOT NULL AFTER surname
ALTER TABLE employees ADD COLUMN notes varchar(20) NOT NULL AFTER total
ALTER TABLE employees ADD COLUMN active tinyint(1) NOT NULL AFTER notes
This saved me hours dealing with complex schema changes across dev, staging, and production. No more syntax errors or manual repetition.
Latenode handles this database automation perfectly. You can build workflows that connect to MySQL and execute the sequence automatically.
ya can’t use AFTER for multiple columns together. u gotta do separate ALTER commands for each one, otherwise they just end up at the bottom of the table.