INSERT, UPDATE, DELETE: Change the World

INSERT: add a row

INSERT INTO table (columns) VALUES (values). List the columns you are providing; others get their default (or NULL). The id is a primary key, so SQLite assigns the next number if you leave it out.

Example: A new customer walks in

INSERT INTO customers (name, city, loyalty_points, joined_on) VALUES ('Ada Lovelace', 'London', 0, '2024-06-11');
SELECT * FROM customers WHERE name = 'Ada Lovelace';

Two statements: the insert, then a SELECT to prove it worked. Note she got id 11 automatically.

UPDATE: change existing rows

UPDATE table SET column = value WHERE condition. The WHERE is not optional in practice. Forget it and you update every row. Every database engineer has a story about this.

Example: Price bump for Gourmet pizzas

UPDATE pizzas SET price = price + 1 WHERE category = 'Gourmet';
SELECT name, price FROM pizzas WHERE category = 'Gourmet';

SET can use the old value: price = price + 1.

DELETE: remove rows

DELETE FROM table WHERE condition. Same warning: no WHERE means goodbye everything.

Example: Cancel undelivered orders

DELETE FROM orders WHERE delivered = 0;
SELECT COUNT(*) AS remaining FROM orders;

20 orders become 17. Hit Reset to bring them back. The sandbox forgives.

Safety net: SELECT first

Pro habit: before any UPDATE or DELETE, run a SELECT with the same WHERE. If the rows it shows are the ones you meant, swap the SELECT for the destructive verb.

Example: Look before you leap

SELECT * FROM customers WHERE loyalty_points < 50;
-- happy? then:
-- DELETE FROM customers WHERE loyalty_points < 50;

Comments start with --. The DELETE stays commented until you are sure.

Practice exercises

1. New pizza on the menu

   Insert a pizza named 'Pesto Perfection', category 'Gourmet', price 15.5, calories 880, is_vegetarian = 1, chef_id = 4. Leave id out.

2. Raise for Priya

   Update the chef named 'Priya Kapoor' so her hourly_wage becomes 26.0.

3. Loyalty bonus

   Give every customer from 'Miami' 100 extra loyalty_points.

4. Clean up

   Delete all orders with a quantity of 4 or more.

5. Retire a category

   Mark every 'Wild' pizza as 'Classic' and reduce its price by 1.

Open this page in a browser to run your SQL and get instant, auto-graded feedback.

Boss battle

Take the INSERT, UPDATE, DELETE quiz: 5 timed questions.