Level 5 · Add columns with ALTER TABLE

ALTER TABLE changes a table’s structure after it exists: add a column, drop one, rename one. Analysts use it mostly on their own staging tables, to enrich them step by step.

ADD COLUMN with a DEFAULT

ALTER TABLE product_stage ADD COLUMN price_tier VARCHAR DEFAULT 'standard';
UPDATE product_stage SET price_tier = 'premium' WHERE unit_price >= 500;

The new column appears on every existing row with the default value. Then a single UPDATE changes the exceptions. Of the 108 active products, 15 cost $500 or more and become premium: 9 Furniture and 6 Outdoor products.

Without DEFAULT, the new column would be NULL everywhere until you fill it. Sometimes that is what you want (“not known yet”), but for counts it is a trap, as the next column shows.

Fill a column from another table: UPDATE … FROM

ALTER TABLE product_stage ADD COLUMN units_dec INTEGER DEFAULT 0;
UPDATE product_stage AS s
SET units_dec = u.units
FROM (SELECT i.product_id, SUM(i.quantity) AS units
      FROM order_items AS i JOIN orders AS o ON o.order_id = i.order_id
      WHERE o.status = 'completed' AND i.quantity > 0
        AND o.order_date >= '2025-12-01' AND o.order_date < '2026-01-01'
      GROUP BY i.product_id) AS u
WHERE u.product_id = s.product_id;

UPDATE ... FROM joins the table being updated to another source; the WHERE is the join condition. Products with no December line have no match, so the UPDATE skips them and they keep the default 0. In the result, 5 active products sold nothing in December 2025: one premium Furniture product and four Outdoor products (two premium, two standard), which fits an outdoor range in winter.

Had the column been NULL for those five, SUM would still be right, but COUNT(CASE WHEN units_dec = 0 THEN 1 END) would report 0 unsold products. A default of 0 states a fact (“sold zero”), where NULL says “unknown”.

Other ALTER TABLE changes

• ALTER TABLE t DROP COLUMN c removes a column.
• ALTER TABLE t RENAME COLUMN a TO b renames one.
• ALTER TABLE t ALTER COLUMN c TYPE DECIMAL(10, 2) changes a type (DuckDB and Postgres syntax).
• ALTER TABLE t RENAME TO t2 renames the table.

On shared tables, structure changes break every query that uses the old name or type, so they go through whoever owns the table. On your own staging tables, change freely.

Common mistakes

• Adding a column without a default and then counting zeros that are actually NULLs.
• Forgetting the join condition in UPDATE ... FROM. Without it, every row is matched against every subquery row and gets an arbitrary value.
• Putting price_tier logic in two places (the UPDATE and later reports) so they drift apart. Store it once in the staging table.

In SQL Server

Adding a column has no COLUMN keyword, and a default is often the current time:

ALTER TABLE dbo.ProductStage ADD ImportedAt DATETIME2 DEFAULT GETDATE();

Existing rows get NULL unless you add WITH VALUES. T-SQL also uses UPDATE s SET ... FROM dbo.ProductStage AS s JOIN (...) AS u ON ..., with the join written out in the FROM.