Published lesson · 30 minutes

Add, update and delete safely

Objective: Use explicit columns and a narrow WHERE condition to change a synthetic SQLite record safely.

Understand the idea

Add, update and delete safely works best when the learner can explain the reason behind each step, not just copy a result. A write statement changes stored data, so a matching SELECT first helps confirm exactly which rows a later update or delete will affect.

A learner corrects the category of one practice inventory item and checks the row before and after the change. Treat the names and details as invented practice information; the example is for learning a method and does not describe a live service.

A dependable sequence is: Begin a transaction when supported, run a SELECT with the intended key, apply the smallest update and inspect the result before commit. Write down the important decision before you begin so another person can follow the reasoning.

Never run a broad update or delete without a condition. Practice only on the resettable synthetic database provided in the workspace. Compare the result with the objective, then change one thing at a time if the first attempt does not work.

Write the SELECT that previews one target row before you draft its update. Explain one assumption that would need checking before anyone used a similar result outside the classroom.

Keep a short record of the choices you made and the evidence you used to check them. A useful practice result is understandable by another learner, stays within the stated scope and makes its limitations visible before anyone relies on it.

Worked examples

Add, update and delete safely: practice example

Explore how add, update and delete safely supports the lesson objective with a small synthetic example.

inventory.sql

CREATE TABLE practice_items (id INTEGER PRIMARY KEY, category TEXT);
INSERT INTO practice_items (id, category) VALUES (1, "Tools");
SELECT id, category FROM practice_items WHERE id = 1;
UPDATE practice_items SET category = "Garden tools" WHERE id = 1;
SELECT id, category FROM practice_items WHERE id = 1;

Practice tasks

  1. Explore add, update and delete safely

    Use the worked example for “Add, update and delete safely”. Change one small detail to suit a different synthetic community situation, then explain what you expected and how you checked whether the change still met the lesson objective.

  2. Build a SQL practice example

    Create a small practice result that demonstrates this objective: Use explicit columns and a narrow WHERE condition to change a synthetic SQLite record safely. Use only synthetic information, keep the first version focused, and write down one limitation that a later version should address.

  3. Review and explain the result

    Review your “Add, update and delete safely” result as if another learner will use it without your explanation. Identify one strength, one exact point that may be unclear, and a revision that would make the result easier to understand or use.

Add, update and delete safely becomes easier when you begin a transaction when supported, check the result and explain the choice to another learner.

Course overview