INSERT, UPDATE, DELETE
Modify data: insert rows, update existing rows, and delete with safety.
What you'll learn
- INSERT syntax for single and multiple rows
- UPDATE with SET and WHERE to modify existing rows
- DELETE with WHERE to remove rows safely
- Using RETURNING to get back inserted/updated rows
- Upsert patterns: INSERT ... ON CONFLICT
Concept
INSERT
INSERT INTO table (col1, col2) VALUES (val1, val2) adds a new row. You can insert multiple rows in one statement by listing multiple value tuples: VALUES (1, 'a'), (2, 'b'), (3, 'c'). This is much faster than separate INSERT statements because it is a single round-trip to the database. If you omit column names, you must provide values for every column in table order — always specify columns explicitly for clarity and safety.
UPDATE
UPDATE table SET col = value WHERE condition modifies existing rows. The SET clause can update multiple columns: SET name = 'new', price = 10. Always include a WHERE clause — without it, every row in the table is updated. A good habit is to first run a SELECT with the same WHERE to preview which rows will be affected.
DELETE
DELETE FROM table WHERE condition removes rows. Like UPDATE, always use WHERE. In practice, many applications use "soft deletes" — a deleted_at timestamp column — instead of actually removing rows, so data can be recovered. Hard deletes are permanent.
RETURNING
PostgreSQL and SQLite support RETURNING * after INSERT, UPDATE, or DELETE. This returns the affected rows in one statement, which is very useful when you insert a row with an auto-generated ID and immediately need that ID: INSERT INTO users (name) VALUES ('Ada') RETURNING id.
Upsert (ON CONFLICT)
INSERT ... ON CONFLICT DO UPDATE (PostgreSQL) or INSERT ... ON DUPLICATE KEY UPDATE (MySQL) lets you insert a row, and if it already exists (conflict on a unique key), update it instead. This is the standard "upsert" pattern for syncing data.
Example
-- Insert multiple rows in one statement
INSERT INTO products (name, price, category, in_stock)
VALUES
('Keyboard', 49.99, 'electronics', TRUE),
('Mousepad', 9.99, 'accessories', TRUE),
('USB Cable', 5.99, 'accessories', FALSE);
-- Update with RETURNING (PostgreSQL)
UPDATE products
SET price = 39.99, in_stock = TRUE
WHERE name = 'Keyboard'
RETURNING id, name, price;
-- Delete safely with a WHERE clause
DELETE FROM products
WHERE in_stock = FALSE AND price < 10;
-- Upsert: insert or update on conflict
INSERT INTO products (name, price, category)
VALUES ('Keyboard', 34.99, 'electronics')
ON CONFLICT (name)
DO UPDATE SET price = EXCLUDED.price;
Four DML patterns: bulk insert, update with RETURNING, safe delete with WHERE, and upsert with ON CONFLICT.
Try it
- SQL Formatter
Format INSERT, UPDATE, and DELETE statements.
- SQL Escaper
Escape user input before inserting into SQL strings.
Common mistakes
The mistake
Running UPDATE or DELETE without WHERE
The fix
Without WHERE, every row is affected. Always include a WHERE clause. Run a SELECT with the same WHERE first to preview affected rows.
The mistake
Inserting user input directly into SQL strings
The fix
This causes SQL injection. Use parameterized queries / prepared statements instead of string concatenation. Never trust user input.
Practice INSERT, UPDATE, DELETE
8 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.