ALTER vs. UPDATE: Key SQL Commands Explained
ALTER restructures a table—adding columns, changing data types, or renaming it—while UPDATE edits the data inside existing rows, keeping the table’s shape untouched.
Picture your kitchen: ALTER remodels the cabinets, UPDATE swaps the spices. Both alter something, so newcomers hear “change” and assume the words are interchangeable, leading to swapped syntax and puzzling errors.
Key Differences
ALTER is a DDL command—think architects drawing new walls—executed once and recorded in the schema. UPDATE is DML—like chefs restocking ingredients—running row-by-row and logged for rollback. One changes design, the other content.
Which One Should You Choose?
Need a new column, index, or rename? Use ALTER. Just fixing or mass-editing stored values? Use UPDATE. Trying UPDATE to add a column throws an error; trying ALTER to fix typos in rows does nothing.
Examples and Daily Life
ALTER TABLE users ADD COLUMN newsletter BOOLEAN; UPDATE users SET newsletter = TRUE WHERE email LIKE ‘%@news.com’; first reshapes the table, then fills the fresh column with intent.
Can ALTER change data values?
No—ALTER tweaks structure, not content. To change values, pair it with UPDATE or use DEFAULT constraints.
Does UPDATE lock the whole table?
It depends on the engine; InnoDB locks affected rows, allowing reads, while MyISAM may lock the entire table.
Can I undo an UPDATE?
Yes—within a transaction you can ROLLBACK. Once committed, you need backups or binary logs to restore prior data.