SQL Basics · Lesson 8 of 9

Changing Data

Reading is only one of the things SQL does. Three more statements change the data in a table.

INSERT: add a row

INSERT INTO products (name, category, price, stock)
VALUES ('Pencil Case', 'Stationery', 7.50, 40);

List the columns, then give one value for each, in the same order. The id is left out here: the database numbers new rows by itself.

UPDATE: change rows

UPDATE products
SET price = 5.00
WHERE name = 'Notebook';

SET says what to change, and WHERE says in which rows. Change several columns by separating them with commas: SET price = 5.00, stock = 100. A new value may use the old one: SET stock = stock - 1.

DELETE: remove rows

DELETE FROM products
WHERE stock = 0;

The WHERE is not optional

Technically it is, and that is the danger. UPDATE and DELETE without a WHERE apply to every row in the table. Somewhere in the world, at this moment, someone is discovering this on a real database.

A safe habit: first write a SELECT with your WHERE to see which rows it finds. Then turn it into the UPDATE or DELETE.

Several statements

You can run several statements in one go, each ending with a semicolon. They run from top to bottom. Here every run starts with a fresh copy of the database, so you cannot do any lasting harm.

Tables in the practice database
TableColumns
customersid, name, city, joined
productsid, name, category, price, stock
ordersid, customer_id, product_id, quantity, ordered

Every run starts from a fresh copy of the database, so nothing you do can break it.

Your Task

  1. Insert a new product: Travel Pillow in the category Travel, with a price of 22 and a stock of 15.
  2. The water bottles have arrived. Update the product Water Bottle so its stock is 50.
  3. Delete every product that still has a stock of 0.
  4. Finish with SELECT * FROM products; to see the result.
  • Travel Pillow is added, in Travel, with a price of 22 and a stock of 15
  • Water Bottle now has a stock of 50, and no other stock was changed
  • The products with no stock are deleted, and only those
  • The last statement shows the whole products table