Introduction

Most SQL databases support altering multiple columns in a single ALTER TABLE statement, but the syntax varies by database. In MySQL, use ALTER TABLE t MODIFY col1 new_type, MODIFY col2 new_type. In PostgreSQL, use ALTER TABLE t ALTER COLUMN col1 TYPE new_type, ALTER COLUMN col2 TYPE new_type. In SQL Server, you must run separate ALTER TABLE ALTER COLUMN statements for each column. Altering data types on columns with existing data requires careful planning — the database must be able to convert existing values to the new type, or the operation fails.

MySQL: Multiple Columns in One Statement

1-- MySQL uses MODIFY COLUMN
2ALTER TABLE employees
3    MODIFY COLUMN salary DECIMAL(12, 2),
4    MODIFY COLUMN bonus DECIMAL(10, 2),
5    MODIFY COLUMN department VARCHAR(100);
6
7-- With CHANGE (also renames the column)
8ALTER TABLE employees
9    CHANGE COLUMN old_name new_name VARCHAR(255),
10    CHANGE COLUMN status status ENUM('active', 'inactive', 'archived');
11
12-- Verify changes
13DESCRIBE employees;

MySQL's ALTER TABLE ... MODIFY changes the column data type. Multiple MODIFY clauses can be comma-separated in one statement, which is faster than running separate ALTER TABLE commands because MySQL only rebuilds the table once.

PostgreSQL: ALTER COLUMN TYPE

1-- PostgreSQL uses ALTER COLUMN ... TYPE
2ALTER TABLE employees
3    ALTER COLUMN salary TYPE DECIMAL(12, 2),
4    ALTER COLUMN bonus TYPE DECIMAL(10, 2),
5    ALTER COLUMN department TYPE VARCHAR(100);
6
7-- With explicit USING clause for type conversion
8ALTER TABLE orders
9    ALTER COLUMN price TYPE NUMERIC(10, 2) USING price::NUMERIC(10, 2),
10    ALTER COLUMN quantity TYPE INTEGER USING quantity::INTEGER,
11    ALTER COLUMN created_at TYPE TIMESTAMPTZ USING created_at AT TIME ZONE 'UTC';
12
13-- Verify
14\d employees

PostgreSQL's USING clause specifies how to convert existing data. Without it, PostgreSQL uses a default cast, which may fail for incompatible types (e.g., TEXT containing non-numeric values to INTEGER).

SQL Server: Separate Statements Required

1-- SQL Server does NOT support multiple ALTER COLUMN in one statement
2-- Each column requires its own ALTER TABLE
3ALTER TABLE employees ALTER COLUMN salary DECIMAL(12, 2);
4ALTER TABLE employees ALTER COLUMN bonus DECIMAL(10, 2);
5ALTER TABLE employees ALTER COLUMN department NVARCHAR(100);
6
7-- To run as a batch
8BEGIN TRANSACTION;
9    ALTER TABLE employees ALTER COLUMN salary DECIMAL(12, 2);
10    ALTER TABLE employees ALTER COLUMN bonus DECIMAL(10, 2);
11    ALTER TABLE employees ALTER COLUMN department NVARCHAR(100);
12COMMIT;
13
14-- Verify
15EXEC sp_columns employees;

SQL Server requires a separate ALTER TABLE ... ALTER COLUMN for each column. Wrap them in a transaction to ensure all changes succeed or none do.

Oracle: Multiple Columns in One Statement

1-- Oracle uses MODIFY with parentheses
2ALTER TABLE employees MODIFY (
3    salary NUMBER(12, 2),
4    bonus NUMBER(10, 2),
5    department VARCHAR2(100)
6);
7
8-- Verify
9DESCRIBE employees;

Oracle wraps multiple column modifications in parentheses after a single MODIFY keyword.

SQLite: No ALTER COLUMN Support

1-- SQLite does NOT support ALTER COLUMN at all
2-- You must recreate the table
3
4-- Step 1: Create new table with desired types
5CREATE TABLE employees_new (
6    id INTEGER PRIMARY KEY,
7    name TEXT,
8    salary REAL,          -- Changed from INTEGER
9    department TEXT        -- Changed from VARCHAR(50)
10);
11
12-- Step 2: Copy data
13INSERT INTO employees_new (id, name, salary, department)
14SELECT id, name, CAST(salary AS REAL), department
15FROM employees;
16
17-- Step 3: Drop old table
18DROP TABLE employees;
19
20-- Step 4: Rename new table
21ALTER TABLE employees_new RENAME TO employees;

SQLite only supports ALTER TABLE ... RENAME and ALTER TABLE ... ADD COLUMN. Changing column types requires recreating the table entirely.

Handling Data Conversion

1-- PostgreSQL: safe conversion with USING
2ALTER TABLE products
3    ALTER COLUMN price TYPE NUMERIC(10, 2) USING price::NUMERIC,
4    ALTER COLUMN weight TYPE NUMERIC(8, 3) USING
5        CASE WHEN weight ~ '^\d+\.?\d*$' THEN weight::NUMERIC
6             ELSE 0 END;
7
8-- MySQL: check for conversion issues first
9SELECT id, price FROM products
10WHERE price REGEXP '[^0-9.]'  -- Find non-numeric values before converting
11
12-- Then alter
13ALTER TABLE products
14    MODIFY COLUMN price DECIMAL(10, 2);

Always check existing data for compatibility before altering types. A string column containing letters cannot be converted to an integer without handling the invalid values first.

Syntax Comparison Table

Common Pitfalls

• Data loss from narrowing types: Changing VARCHAR(255) to VARCHAR(50) truncates values longer than 50 characters. Changing DECIMAL(10,2) to INTEGER drops decimal places. Always check MAX(LENGTH(column)) or MAX(column) before narrowing a type.
• Foreign key and index conflicts: Columns referenced by foreign keys or indexes may block type changes. Drop the constraints first, alter the column, then recreate the constraints. Some databases (MySQL) handle this automatically, others (PostgreSQL) do not.
• Implicit vs explicit casting failures: PostgreSQL's ALTER COLUMN ... TYPE without USING relies on implicit casts. Converting TEXT to INTEGER fails if any row contains non-numeric text. Always use USING with an explicit cast expression for non-trivial conversions.
• Table lock during ALTER TABLE: Most databases acquire an exclusive lock during ALTER TABLE, blocking all reads and writes. For large tables (millions of rows), this can cause significant downtime. Use tools like pt-online-schema-change (MySQL) or pg_repack (PostgreSQL) for zero-downtime migrations.
• NULL handling in NOT NULL columns: Adding NOT NULL constraint while changing the type fails if the column contains NULL values. Either update NULLs first (UPDATE t SET col = default WHERE col IS NULL) or change the type and constraint in separate steps.

Summary

• MySQL and PostgreSQL support multiple column type changes in one ALTER TABLE statement
• SQL Server requires separate ALTER TABLE ALTER COLUMN statements per column
• SQLite does not support ALTER COLUMN — recreate the table instead
• Use PostgreSQL's USING clause to control data conversion explicitly
• Always check existing data for compatibility before changing types
• Wrap multiple alterations in a transaction to ensure atomicity