DML

INSERT

INSERT INTO Users (Username, Email) VALUES ('alice', 'alice@example.com');

INSERT INTO Users (Username, Email) VALUES
    ('alice', 'alice@example.com'),
    ('bob',   'bob@example.com');

Omitting the column list means positional values in table order, which is fragile against a later ADD COLUMN. Name the columns.

Insert the result of a query:

INSERT INTO UsersArchive (Id, Username)
SELECT Id, Username FROM Users WHERE IsActive = FALSE;

Insert a row made entirely of defaults, which is what you want for a table whose columns are all generated:

INSERT INTO Sessions DEFAULT VALUES;

NOT NULL is still checked, so a table with a non-nullable column and no default still refuses.

RETURNING

Get generated values back as part of the statement, without a second round trip:

INSERT INTO Orders (CustomerId, Total) VALUES (@customerId, @total)
RETURNING Id, CreatedAt;

RETURNING works on INSERT, UPDATE and DELETE, and takes a select list, so expressions are allowed rather than only column names.

Upserts

Two forms. The short one handles a conflict by replacing or skipping the row:

INSERT OR IGNORE  INTO Settings (Key, Value) VALUES ('theme', 'dark');
INSERT OR REPLACE INTO Settings (Key, Value) VALUES ('theme', 'dark');

The long one says what to do, and can name the columns whose conflict it is handling:

INSERT INTO Settings (Key, Value) VALUES ('theme', 'dark')
ON CONFLICT (Key) DO UPDATE SET Value = EXCLUDED.Value;

INSERT INTO Counters (Name, Hits) VALUES ('page', 1)
ON CONFLICT (Name) DO UPDATE SET Hits = Counters.Hits + 1;

INSERT INTO Settings (Key, Value) VALUES ('theme', 'dark')
ON CONFLICT DO NOTHING;

EXCLUDED is the row that would have been inserted, so EXCLUDED.Value is the new value and Counters.Hits is the one already there. A WHERE on the DO UPDATE makes the update conditional.

UPDATE

UPDATE Users SET IsActive = FALSE WHERE LastLoginAt < @cutoff;

An UPDATE with no WHERE updates every row. There is no confirmation step.

Update from another table with FROM:

UPDATE Orders AS o
SET CustomerName = c.Name
FROM Customers AS c
WHERE o.CustomerId = c.Id;

And RETURNING applies here too:

UPDATE Orders SET Status = 'shipped' WHERE Id = @id
RETURNING Id, Status, UpdatedAt;

DELETE

DELETE FROM Sessions WHERE ExpiresAt < NOW();

USING is the delete counterpart of FROM:

DELETE FROM OrderItems AS oi
USING Orders AS o
WHERE oi.OrderId = o.Id AND o.Status = 'cancelled';

TRUNCATE TABLE empties a table without going row by row, and without firing triggers. Use DELETE when the triggers matter.

MERGE

One statement that inserts what is missing, updates what is there, and optionally deletes.

MERGE INTO Products AS target
USING Incoming AS source
    ON target.Sku = source.Sku
WHEN MATCHED THEN
    UPDATE SET Price = source.Price, Stock = source.Stock
WHEN NOT MATCHED THEN
    INSERT (Sku, Name, Price, Stock)
    VALUES (source.Sku, source.Name, source.Price, source.Stock);

The source can be a table or a subquery:

MERGE INTO Inventory AS target
USING (SELECT ProductId, SUM(Quantity) AS Total
       FROM Shipments GROUP BY ProductId) AS source
    ON target.ProductId = source.ProductId
WHEN MATCHED THEN UPDATE SET Quantity = source.Total
WHEN NOT MATCHED THEN INSERT (ProductId, Quantity)
    VALUES (source.ProductId, source.Total);

Each clause can carry its own condition, and a matched row can be deleted rather than updated:

WHEN MATCHED AND source.Discontinued = TRUE THEN DELETE
WHEN MATCHED AND source.Price <> target.Price THEN UPDATE SET Price = source.Price

Clauses are evaluated in order, and the first one whose condition holds wins.

Alias both sides. Unqualified column names in a MERGE are ambiguous by construction, and the aliases are what make each reference say which table it means.

The matched half of a MERGE is an update in every respect: BEFORE UPDATE and AFTER UPDATE triggers fire, INSTEAD OF UPDATE stands in for the write, and UPDATE OF column matches against the columns the SET names.

Triggers and DML

Every statement here fires the table's triggers. A BEFORE trigger can cancel a row, and an INSTEAD OF trigger replaces the write.

TRUNCATE is the exception: it does not walk rows, so no row triggers fire.

Where to go next

• Queries, reading rows back
• DDL, the tables and triggers these statements act on
• Transactions, grouping several of these together
• Functions, what goes in a SET or a VALUES