SQL CHANGE

What does the SQL CHANGE clause do?

MySQL clause inside ALTER TABLE that renames a column and optionally alters its definition and position.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CHANGE: Supported: MySQL 3.23+, MariaDB, Percona Server. Not supported: PostgreSQL, SQL Server, Oracle, SQLite, Standard SQL.

SQL CHANGE Full Explanation

CHANGE is a MySQL specific sub-clause of ALTER TABLE. It lets you rename an existing column, redefine its data type, nullability, default value, and even reposition it within the table in one atomic operation. Because the entire column definition must be restated, omitting any attribute (such as NOT NULL or DEFAULT) will drop that attribute. CHANGE implicitly preserves the current data as long as the new definition is compatible with existing values. The statement acquires a metadata lock and may rebuild the table depending on the change, which can briefly block writes on large tables. InnoDB retains indexes automatically, but renaming a column referenced by indexes or foreign keys will update those objects under the hood. You cannot use CHANGE to rename multiple columns in a single clause, but you can issue multiple CHANGE clauses separated by commas in the same ALTER TABLE.

SQL CHANGE Syntax

ALTER TABLE table_name
  CHANGE [COLUMN] old_column_name new_column_name new_definition
  [FIRST | AFTER column_x];

SQL CHANGE Parameters

table_name (identifier) - Target table.
old_column_name (identifier) - Current column name.
new_column_name (identifier) - Desired column name.
new_definition (column_definition) - Full column type plus constraints (e.g., INT NOT NULL DEFAULT 0).
FIRST (keyword) - Move column to first position.
AFTER column_x (clause) - Place column after another column.

Example Queries Using SQL CHANGE

-- Rename and widen a VARCHAR column
ALTER TABLE customers
  CHANGE last_name surname VARCHAR(160) NOT NULL;

-- Rename and reposition a column
ALTER TABLE orders
  CHANGE COLUMN created_at order_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP FIRST;

-- Rename multiple columns at once
ALTER TABLE products
  CHANGE price unit_price DECIMAL(10,2) NOT NULL,
  CHANGE desc description TEXT AFTER name;

Expected Output Using SQL CHANGE

Column names are updated, definitions altered, and table metadata rebuilt
Existing data remains accessible under new column names

Use Cases with SQL CHANGE

Renaming legacy column names to clearer names.
Enlarging a column length while renaming it.
Adding NOT NULL or DEFAULT while changing the name.
Moving frequently queried columns to the front for readability.

Common Mistakes with SQL CHANGE

Forgetting to repeat the full column definition, causing attributes like NOT NULL or DEFAULT to be dropped.
Using CHANGE in non-MySQL databases where the clause is unsupported.
Attempting to omit new_column_name (use MODIFY instead if you are not renaming).
Overlooking foreign key constraints that may need to be dropped and recreated manually in older MySQL versions.

First Introduced In

MySQL 3.23

Frequently Asked Questions

What happens if I omit NOT NULL or DEFAULT when using CHANGE?

The omitted attribute is dropped because the full column definition is replaced. Always restate every characteristic you want to keep.

Can I rename multiple columns in one ALTER TABLE?

Yes. Separate each CHANGE clause with a comma inside the same ALTER TABLE statement.

Does CHANGE lock the table?

MySQL places a metadata lock and may rebuild the table, blocking writes briefly. Online DDL features in newer MySQL versions reduce downtime but do not eliminate locking entirely.

How is CHANGE different from MODIFY?

MODIFY changes the definition without renaming the column. CHANGE always requires a new column name, even if the name is unchanged.