SQL INSERT, UPDATE and DELETE

INSERT adds rows, UPDATE changes existing rows and DELETE removes rows. Together they're called DML, the Data Manipulation Language. Analysts use them less than SELECT, but you'll need them for analysis tables, fixing data and every SQL interview.

These statements change data. In the SQL Compiler, click Reset Database any time to restore the original ShopKart data.

INSERT

Syntax

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

1. Insert one row

INSERT INTO customers (customer_id, customer_name, email, city, state, signup_date)
VALUES (13, 'Nikhil Joshi', 'nikhil.j@gmail.com', 'Nagpur', 'Maharashtra', '2026-02-01');

SELECT customer_id, customer_name, city, is_prime
FROM customers WHERE customer_id = 13;

Result:

(1 row)

Columns you leave out get their default (is_prime defaults to FALSE) or NULL.

2. Insert several rows at once

INSERT INTO categories (category_id, category_name)
VALUES (7, 'Toys'), (8, 'Grocery');

SELECT * FROM categories WHERE category_id >= 7;

Result:

(2 rows)

3. INSERT ... SELECT

Copy rows produced by a query. Archive cancelled orders into a new table:

CREATE TABLE cancelled_orders (order_id INT, customer_id INT, order_date DATE);

INSERT INTO cancelled_orders (order_id, customer_id, order_date)
SELECT order_id, customer_id, order_date
FROM orders
WHERE status = 'Cancelled';

SELECT * FROM cancelled_orders;

Result:

(2 rows)

4. RETURNING

See what was inserted, including generated values:

INSERT INTO categories (category_id, category_name)
VALUES (9, 'Stationery')
RETURNING *;

Result:

(1 row)

UPDATE

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

5. Update specific rows

Order 1020 has been delivered:

UPDATE orders
SET status = 'Delivered',
    delivery_date = '2025-12-07'
WHERE order_id = 1020
RETURNING order_id, status, delivery_date;

Result:

(1 row)

6. Update using a calculation

A 10% price rise on all Books:

UPDATE products
SET price = ROUND(price * 1.10, 0)
WHERE category_id = 5
RETURNING product_name, price;

Result:

(2 rows)

7. Update using another table

Make every customer who has spent over ₹20,000 on delivered orders a Prime member. PostgreSQL's UPDATE ... FROM joins to another table or subquery:

UPDATE customers c
SET is_prime = TRUE
FROM (
  SELECT o.customer_id
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'Delivered'
  GROUP BY o.customer_id
  HAVING SUM(oi.quantity * oi.unit_price) > 20000
) AS big_spenders
WHERE c.customer_id = big_spenders.customer_id
  AND c.is_prime = FALSE
RETURNING c.customer_name, c.is_prime;

Result:

(1 row)

DELETE

Syntax

DELETE FROM table_name
WHERE condition;

8. Delete specific rows

DELETE FROM categories
WHERE category_id IN (8, 9)
RETURNING *;

Result:

(2 rows)

9. Delete using a subquery

Remove customers who signed up but never ordered (here, Nikhil from example 1 and Divya):

DELETE FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders)
RETURNING customer_id, customer_name;

Result:

(2 rows)

Upsert: insert or update

ON CONFLICT handles "insert this, but if the key already exists, update it instead":

INSERT INTO categories (category_id, category_name)
VALUES (7, 'Toys & Games'), (10, 'Pet Supplies')
ON CONFLICT (category_id)
DO UPDATE SET category_name = EXCLUDED.category_name
RETURNING *;

Result:

(2 rows)

EXCLUDED refers to the row you tried to insert. Category 7 was renamed and category 10 was added.

Common mistakes

UPDATE or DELETE without WHERE changes every row. The safe workflow:

1. Write the WHERE as a SELECT first and check the rows.

2. Run the change inside a transaction.

3. Check the result, then COMMIT (or ROLLBACK).

• Listing values in the wrong column order. Always name the columns in INSERT INTO table (col1, col2).
• Breaking constraints. Deleting a customer who has orders fails because of the foreign key. That's the constraint doing its job.

Practice question

Add a new product, "Yoga Block" (ID 116, Sports, brand Boldfit, price ₹449, cost ₹200, stock 80), then reduce its stock by 5 and return the updated row.

INSERT INTO products (product_id, product_name, category_id, brand, price, cost_price, stock_qty)
VALUES (116, 'Yoga Block', 6, 'Boldfit', 449, 200, 80);

UPDATE products
SET stock_qty = stock_qty - 5
WHERE product_id = 116
RETURNING product_id, product_name, stock_qty;

Key takeaways

• INSERT adds rows; name the columns explicitly. INSERT ... SELECT copies query results.
• UPDATE ... SET ... WHERE changes rows; UPDATE ... FROM uses other tables.
• DELETE ... WHERE removes rows. Forgetting WHERE affects the whole table.
• RETURNING shows changed rows; ON CONFLICT turns an insert into an upsert.