Learn

SQL has four core data operations — together called DML (Data Manipulation Language):

• INSERT — add rows
• UPDATE — change existing rows
• DELETE — remove rows
• SELECT — read rows (you've been doing this)

INSERT

INSERT INTO users (name, age) VALUES ('Ada', 36);
INSERT INTO users VALUES (1, 'Linus', 53);                     -- positional
INSERT INTO users (name, age) VALUES                            -- multi-row
    ('Grace', 85),
    ('Donald', 84),
    ('Edsger', 72);

If you list columns, only those need values. Other columns get their default (or NULL if no default).

INSERT from SELECT — copy or transform:

INSERT INTO archived_users (id, name)
SELECT id, name FROM users WHERE last_login < '2020-01-01';

UPDATE

UPDATE users SET age = age + 1;                                 -- ALL rows
UPDATE users SET age = age + 1 WHERE id = 1;                    -- one row
UPDATE users SET age = 30, name = 'Ada L.' WHERE id = 1;        -- multiple cols

Common bug: forgetting WHERE updates EVERY row. Always test the WHERE with a SELECT first:

-- preview:
SELECT * FROM users WHERE id = 1;
-- then update:
UPDATE users SET age = 30 WHERE id = 1;

DELETE

DELETE FROM users;                                              -- ALL rows!
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE last_login < '2020-01-01';

Same footgun as UPDATE — preview the WHERE first.

TRUNCATE (Postgres, MySQL) — fast empty-the-table; can't be rolled back in some engines. SQLite uses DELETE FROM (which is also fast on small tables).

RETURNING (Postgres, SQLite 3.35+)

INSERT INTO users (name) VALUES ('Ada') RETURNING id;
DELETE FROM old_logs WHERE created_at < '2020-01-01' RETURNING *;

Get back the rows you just touched — saves a round-trip.

UPSERT — INSERT OR UPDATE

SQLite's INSERT ... ON CONFLICT DO UPDATE:

INSERT INTO config (key, value) VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = excluded.value;

excluded is a special name for "the row we tried to insert". This is the same syntax in Postgres and modern SQLite. MySQL uses INSERT ... ON DUPLICATE KEY UPDATE.

Bulk operations

Multi-row INSERT is much faster than N single-row INSERTs:

-- slow: N round-trips
INSERT INTO t VALUES (1);
INSERT INTO t VALUES (2);
INSERT INTO t VALUES (3);

-- fast: 1 statement
INSERT INTO t VALUES (1), (2), (3);

For real bulk loads, every DB has a fast-path:

• Postgres: COPY FROM STDIN
• MySQL: LOAD DATA INFILE
• SQLite: .import or BEGIN TRANSACTION around many INSERTs