Data Modification

INSERT INTO table (col1, col2) VALUES (val1, val2);

INSERT INTO users (first_name, email, created_at)
VALUES ('Alice', '[email protected]', CURRENT_TIMESTAMP);

-- 1 row inserted

Note Always specify column names explicitly. Relying on column order breaks when the table schema changes. Strings must be in single quotes - double quotes are for identifiers in ANSI SQL.

INSERT INTO table (col1, col2)
VALUES (val1, val2), (val3, val4), ...;

INSERT INTO products (product_name, price, category_id)
VALUES
  ('Wireless Mouse', 29.99, 2),
  ('USB Keyboard', 49.99, 2),
  ('27" Monitor', 349.00, 3);

-- 3 rows inserted

Note Multi-row INSERT is much faster than individual INSERTs because it reduces round trips and allows batch optimization. Most databases support this syntax. There may be a limit on the number of rows per statement.

UPDATE table SET col1 = val1, col2 = val2 WHERE condition;

UPDATE products
SET price = price * 1.10,
    updated_at = CURRENT_TIMESTAMP
WHERE category_id = 5;

-- Increases price by 10% for all products in category 5

Note Always include a WHERE clause unless you genuinely want to update every row. Running UPDATE without WHERE is a common disaster. Test with a SELECT using the same WHERE first.

-- PostgreSQL
UPDATE table1 SET col = t2.col
FROM table2 t2 WHERE table1.ref = t2.id;
-- MySQL
UPDATE table1 t1 JOIN table2 t2 ON t1.ref = t2.id
SET t1.col = t2.col;

-- PostgreSQL
UPDATE orders
SET shipping_region = u.region
FROM users u
WHERE orders.user_id = u.id
  AND orders.shipping_region IS NULL;

-- Fills in missing shipping regions from the users table

Note Syntax differs between databases. PostgreSQL uses UPDATE ... FROM. MySQL uses UPDATE ... JOIN. Standard SQL uses a correlated subquery in SET. Always test with a SELECT first.

DELETE FROM table WHERE condition;

DELETE FROM sessions
WHERE last_active < CURRENT_DATE - INTERVAL '90 days';

-- Removes sessions inactive for over 90 days

Note DELETE without WHERE deletes ALL rows. Use TRUNCATE TABLE for faster full-table deletion (it resets auto-increment too). DELETE fires row-level triggers; TRUNCATE usually does not.

INSERT INTO table (columns) VALUES (values)
ON CONFLICT (conflict_column)
DO UPDATE SET col = EXCLUDED.col;

INSERT INTO user_preferences (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON CONFLICT (user_id)
DO UPDATE SET
  theme = EXCLUDED.theme,
  language = EXCLUDED.language;

-- Inserts if user_id 42 has no row, otherwise updates

Note EXCLUDED refers to the row that was proposed for insertion. You need a unique constraint or unique index on the conflict column for ON CONFLICT to work.

INSERT INTO table (columns) VALUES (values)
ON DUPLICATE KEY UPDATE col = VALUES(col);

INSERT INTO user_preferences (user_id, theme, language)
VALUES (42, 'dark', 'en')
ON DUPLICATE KEY UPDATE
  theme = VALUES(theme),
  language = VALUES(language);

-- Inserts or updates, same as PostgreSQL ON CONFLICT

Note MySQL 8.0.19+ also supports VALUES(col) replacement with the alias syntax: AS new_row followed by new_row.col. The VALUES() function in ON DUPLICATE KEY UPDATE is deprecated in MySQL 8.0.20+.

MERGE INTO target USING source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT (...) VALUES (...);

MERGE INTO inventory t
USING shipments s ON t.product_id = s.product_id
WHEN MATCHED THEN
  UPDATE SET t.quantity = t.quantity + s.quantity
WHEN NOT MATCHED THEN
  INSERT (product_id, quantity)
  VALUES (s.product_id, s.quantity);

-- Updates existing inventory or inserts new product rows

Note MERGE is supported by SQL Server, Oracle, and PostgreSQL 15+. MySQL does not support MERGE - use ON DUPLICATE KEY UPDATE instead. MERGE can include a WHEN MATCHED AND condition for conditional updates.

INSERT INTO table (columns) VALUES (values) RETURNING *;
UPDATE table SET col = val WHERE condition RETURNING col;
DELETE FROM table WHERE condition RETURNING id;

INSERT INTO orders (user_id, total_amount, status)
VALUES (42, 299.99, 'pending')
RETURNING order_id, created_at;

-- order_id | created_at
-- 1847     | 2025-11-20 14:35:00

Note RETURNING avoids a separate SELECT to get generated IDs or default values. Supported in PostgreSQL natively. MySQL 8.0 does not support RETURNING - use LAST_INSERT_ID() instead. SQL Server uses the OUTPUT clause.

TRUNCATE TABLE table_name;

TRUNCATE TABLE temp_import_data;

-- All rows removed instantly, auto-increment reset

Note TRUNCATE is much faster than DELETE for removing all rows because it deallocates data pages instead of logging individual row deletions. It cannot be rolled back in MySQL (it can in PostgreSQL). It also cannot have a WHERE clause.