Skip to content

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

Practice INSERT, UPDATE, DELETE

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors