lesson 60 Database engineering · advanced

DML: INSERT, UPDATE & DELETE

Insert, update and delete records safely.

the idea

DML changes rows: INSERT adds, UPDATE modifies matching rows, DELETE removes matching rows. Every UPDATE and DELETE should have a WHERE clause that names exactly the rows you mean; run the same condition as a SELECT first to see what it will touch.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Preview rows before you change them. Run the same WHERE you will use in UPDATE as a SELECT first. You see every Pets product (category 13) before anything is modified.the pattern

   example.sql Run

   sql›

   SELECT stock_code, description, list_price
   FROM products
   WHERE category_id = 13
   ORDER BY stock_code;

2. 2

   Update prices and check the result. The script bumps cheap Pets lines by £1, then SELECTs the category again so you can compare before and after in one disposable run.

   step-2.sql Run

   sql›

   UPDATE products
   SET list_price = list_price + 1
   WHERE category_id = 13 AND list_price < 2;
   SELECT stock_code, list_price
   FROM products
   WHERE category_id = 13
   ORDER BY stock_code;

common mistake

UPDATE products SET list_price = list_price * 1.1 without WHERE reprices the entire catalog.