These are the DML (Data Manipulation Language) commands that change data: INSERT adds rows, modifies existing rows, removes rows. Unlike (which only reads), these modify the database — so they require care, especially the clause.
These are the DML (Data Manipulation Language) commands that change data: INSERT adds rows, modifies existing rows, removes rows. Unlike (which only reads), these modify the database — so they require care, especially the clause.
UPDATEDELETESELECTWHERE-- insert one row
INSERT INTO users (name, email, age)
VALUES ('Ann', '[email protected]', 30);
-- insert multiple rows at once (efficient)
INSERT INTO users (name, email)
VALUES ('Bob', '[email protected]'), ('Carol', '[email protected]');
-- insert from a query
INSERT INTO archived_users SELECT * FROM users WHERE inactive = true;
You specify the columns and values. Inserting multiple rows in one statement is more efficient than many separate inserts.
UPDATE users
SET age = 31, updated_at = NOW() -- set one or more columns
WHERE id = 5; -- ❗ WHICH rows to update
-- ❌ DANGER — no WHERE clause updates EVERY row in the table!
UPDATE users SET active = false; -- sets ALL users inactive 😱
Critical: an UPDATE (or DELETE) without a WHERE clause affects every row in the table. Forgetting WHERE is a catastrophic, common mistake — always double-check it.
DELETE FROM users WHERE id = 5; -- delete a specific row
DELETE FROM users WHERE inactive = true; -- delete matching rows
-- ❌ DANGER — no WHERE deletes the ENTIRE table's contents!
DELETE FROM users;
✓ ALWAYS include a WHERE clause (unless you truly mean to affect all rows)
✓ Test the WHERE first with SELECT (SELECT * WHERE ... → see what you'd affect)
✓ Use TRANSACTIONS (BEGIN ... COMMIT/ROLLBACK) so you can undo mistakes
✓ DELETE vs TRUNCATE: TRUNCATE quickly empties a whole table (faster, but not row-by-row,
can't be rolled back in some DBs, resets auto-increment)
INSERT, UPDATE, and DELETE are how applications change data — fundamental to any application that stores and modifies information (creating records, editing, removing), so understanding them is essential everyday knowledge alongside SELECT.
Knowing how to insert (single, multiple, or from a query), update (setting columns), and delete rows is necessary for all data-modifying operations.
The most critical point is the danger of a missing WHERE clause: an UPDATE or DELETE without WHERE affects every row in the table — a catastrophic, surprisingly common mistake that can wipe out or corrupt all data.
Understanding this danger and the safety practices (always include WHERE, test the condition with a SELECT first, use transactions so mistakes can be rolled back) is vital knowledge that prevents serious data-loss accidents.
Knowing related details (efficient multi-row inserts, DELETE vs TRUNCATE for emptying tables) rounds out the practical knowledge.
Since modifying data is fundamental to applications, and since the missing-WHERE mistake is both catastrophic and common (a frequent cause of real production data disasters), mastering these DML commands — and especially the critical discipline around the WHERE clause and transactional safety — is core, must-know knowledge for anyone who writes data-changing SQL, where the stakes (irreversible data loss) make the safety practices as important as the syntax.