🎓 A1Learner
🔍

SQL Alter Table

ALTER TABLE changes a table that already exists. ADD COLUMN grows the table in place, and the old rows stay.

Add a column

The suppliers table gains an email column. Every old row holds NULL in it.

ALTER TABLE suppliers ADD COLUMN email TEXT;
SELECT supplier_id, name, email FROM suppliers ORDER BY supplier_id;

Fill one row

An UPDATE fills the new column for one supplier. The SELECT reads that row back.

ALTER TABLE suppliers ADD COLUMN email TEXT;
UPDATE suppliers SET email = 'sales@paperco.example' WHERE supplier_id = 1;
SELECT name, email FROM suppliers WHERE supplier_id = 1;

Example

ALTER TABLE suppliers ADD COLUMN email TEXT;
UPDATE suppliers SET email = 'contact@bagworld.example' WHERE name = 'Bag World';
SELECT supplier_id, name, email FROM suppliers ORDER BY supplier_id;

Key points

  • ALTER TABLE: the statement that changes an existing table
  • Use ADD COLUMN to add one column to a table.
  • The new column needs a name and a type.
  • Rows that already exist hold NULL in the new column.
  • An UPDATE can fill the new column for chosen rows.

In this example, ALTER TABLE adds an email column to suppliers. The UPDATE fills the email for Bag World only. The SELECT shows the new column, with NULL where no email was set.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

What does ALTER TABLE with ADD COLUMN do?