SovranCode
SQL: Query, Model, and Analyze Data INSERT, UPDATE, and DELETE
This device
Course contentsINSERT, UPDATE, and DELETE · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
SQL: Query, Model, and Analyze Data32 complete · 0 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
PREVIOUS LESSONViews and materialized views
NEXT LESSONUpserts and conflict handling
Writing and protecting data · Lesson 21 155 min

INSERT, UPDATE, and DELETE

A write is a decision about which rows change and which stay still. Name the columns you insert, preview the WHERE population before you update or delete, then read the table back so the stored result—not the hope—is what you trust.

Reads are part of writing

Production habits are boring on purpose. Select the target rows. Confirm the count. Run the write. Select again. The next lesson covers upserts; this one makes the three basic verbs safe enough to use on real data.

INSERT adds rows; named columns keep the mapping stable

INSERT appends new records. List the destination columns so each value has a name, not a position. Here is_active is omitted, so the table default of 1 fills it. If someone later adds a column to products, this statement still means the same three facts.

SQL BROWSER RUNNER

Insert catalog rows with named columns

Add two products and let the default publication flag apply.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0),
  is_active INTEGER NOT NULL DEFAULT 1
);

INSERT INTO products (product_id, product_name, price_cents)
VALUES
  (1, 'SQL notebook', 1499),
  (2, 'Database sticker', 299);

SELECT product_id, product_name, price_cents, is_active
FROM products
ORDER BY product_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which column was omitted from the INSERT, and what value did the table store for it?

INSERT...SELECT copies a query result

You do not have to type every row. INSERT ... SELECT takes the rows a query produces and appends them. That is how a reviewed draft list becomes a catalog, or how yesterday's report becomes an archive. The WHERE on the SELECT is the filter that decides which drafts are ready.

SQL BROWSER RUNNER

Publish drafts that meet a price rule

Copy selected draft rows into the catalog in one statement.

CREATE TABLE drafts (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

CREATE TABLE catalog (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

INSERT INTO drafts VALUES
  (1, 'SQL notebook', 1499),
  (2, 'Schema poster', 899);

INSERT INTO catalog (product_id, product_name, price_cents)
SELECT product_id, product_name, price_cents
FROM drafts
WHERE price_cents >= 1000;

SELECT product_id, product_name, price_cents
FROM catalog
ORDER BY product_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Why is the schema poster absent from catalog, and how would you include it on purpose?

Preview UPDATE and DELETE with the same WHERE

Before changing production rows, run a SELECT that uses the same predicate you plan to write. If the preview shows the wrong product, the update would have been wrong too. Treat “I meant that one row” as a claim you prove, not a feeling.

SQL BROWSER RUNNER

Select the row you intend to change

Inspect product 1 before touching its price.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1499),
  (2, 'Database sticker', 299),
  (3, 'Schema poster', 899);

SELECT product_id, product_name, price_cents
FROM products
WHERE product_id = 1;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: How many rows does this WHERE match? What would you do if it matched more than one?

UPDATE changes existing rows

SET names the new values. WHERE names the population. After the write, select the whole table so you can see that product 1 moved to 1299 cents and the others did not. Verification is cheaper than an incident.

SQL BROWSER RUNNER

Change one product price

Update a single product, then read every row to confirm the blast radius.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1499),
  (2, 'Database sticker', 299),
  (3, 'Schema poster', 899);

UPDATE products
SET price_cents = 1299
WHERE product_id = 1;

SELECT product_id, product_name, price_cents
FROM products
ORDER BY product_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which rows kept their original price, and which clause protected them?

A missing WHERE is a table-wide write

UPDATE products SET price_cents = 100 is legal SQL. It is also how catalogs lose every real price in one statement. If you truly mean every row, say so in a comment and in the preview. If you do not, the statement is unfinished.

SQL BROWSER RUNNER

See an UPDATE without WHERE

Run the dangerous form once, on toy data, so the result is unforgettable.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1499),
  (2, 'Database sticker', 299),
  (3, 'Schema poster', 899);

UPDATE products
SET price_cents = 100;

SELECT product_id, product_name, price_cents
FROM products
ORDER BY product_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: How many prices became 100? Rewrite the statement so only the sticker changes.

Constraints still reject bad writes

Inserts and updates are not exempt from the last lesson's rules. A negative price should fail CHECK (price_cents >= 0). Do not turn the rule off. Change the value, or change the schema if the rule itself is wrong.

SQL BROWSER RUNNER

Predict a rejected price change

Start from a valid product, then attempt an illegal write in the editor.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents >= 0)
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1499);

SELECT product_id, product_name, price_cents
FROM products;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add UPDATE products SET price_cents = -1 WHERE product_id = 1. Which constraint should stop it, and what remains in the table?

DELETE removes rows; target them narrowly

Physical delete forgets. Use it when the row should not exist—duplicate imports, true mistakes, data you are not allowed to keep. Combine identifiers and status so a published product cannot vanish because of a copied product_id alone. Preview first, then delete, then select what remains.

SQL BROWSER RUNNER

Delete one inactive product

Remove a specific retired row and confirm the others remain.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  is_active INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1),
  (2, 'Old sticker', 0),
  (3, 'Schema poster', 1);

DELETE FROM products
WHERE product_id = 2 AND is_active = 0;

SELECT product_id, product_name, is_active
FROM products
ORDER BY product_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Why does the WHERE include both product_id and is_active, and what would DELETE FROM products have done?

A status change keeps history

If orders, reports, or support tickets still refer to a product, deleting the row creates orphans or broken history. Setting is_active = 0 hides it from the live catalog while leaving the fact in place. That is a soft delete. Choose it when the record still has meaning.

SQL BROWSER RUNNER

Retire a product without removing it

Mark one product inactive and keep the row for later explanation.

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  is_active INTEGER NOT NULL
);

INSERT INTO products VALUES
  (1, 'SQL notebook', 1),
  (2, 'Old sticker', 1),
  (3, 'Schema poster', 1);

UPDATE products
SET is_active = 0
WHERE product_id = 2;

SELECT product_id, product_name, is_active
FROM products
ORDER BY product_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: How would a storefront query hide product 2, and why can a report still join to it?

A write checklist

  1. State the one-row meaning of the table you are changing.
  2. For INSERT, name columns and let defaults apply only when they are correct.
  3. For UPDATE or DELETE, SELECT with the same WHERE and check the row count.
  4. Run the write.
  5. SELECT the result, including nearby rows that must not have changed.
  6. If a constraint fails, treat it as information, not an obstacle to silence.

Independent lab: correct a lesson catalog

Start with two published lessons and a broken draft. The provided solution renames and publishes the draft as the views lesson, inserts a throwaway experiment, then deletes that experiment because it was never meant to ship. Read every statement before you run them, then confirm three published-quality rows remain.

SQL BROWSER RUNNER

Repair, publish, and clean a lesson list

Combine INSERT, a targeted UPDATE, and a narrow DELETE on a small catalog.

CREATE TABLE lessons (
  lesson_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('draft', 'published', 'retired')),
  duration_minutes INTEGER NOT NULL CHECK (duration_minutes > 0)
);

INSERT INTO lessons (lesson_id, title, status, duration_minutes)
VALUES
  (1, 'CREATE TABLE and schema design', 'published', 155),
  (2, 'Constraints and data integrity', 'published', 155),
  (3, 'Broken draft title', 'draft', 40);

UPDATE lessons
SET title = 'Views and materialized views',
    status = 'published',
    duration_minutes = 155
WHERE lesson_id = 3 AND status = 'draft';

INSERT INTO lessons (lesson_id, title, status, duration_minutes)
VALUES (4, 'Unused experiment', 'draft', 20);

DELETE FROM lessons
WHERE lesson_id = 4 AND status = 'draft';

SELECT lesson_id, title, status, duration_minutes
FROM lessons
ORDER BY lesson_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which WHERE clause prevented the published lessons from being rewritten, and why was the experiment deleted instead of retired?

Common mistakes to avoid

  • Inserting by column position so a later schema change maps values to the wrong fields.
  • Updating or deleting without a preview SELECT.
  • Omitting WHERE and rewriting the whole table.
  • Deleting a row that other tables or reports still need to explain.
  • Swallowing a constraint error and storing the bad value another way.
  • Trusting the write succeeded without reading the stored rows.

Lesson review

  • I can INSERT with named columns and intentional defaults.
  • I can copy a filtered result with INSERT ... SELECT.
  • I can preview an UPDATE or DELETE with the same WHERE.
  • I can explain why a missing WHERE is a table-wide write.
  • I can choose physical delete versus a status change.
  • I can verify a write by reading the table afterward.
KNOWLEDGE CHECK

Check your write-path reasoning

Answer all ten questions, then revisit the example whose WHERE clause or preview step still feels unclear.

01Why list column names in an INSERT?
02When does DEFAULT fill a column during INSERT?
03What should you do before an UPDATE or DELETE in production data?
04What happens if UPDATE has no WHERE clause?
05What does UPDATE SET price_cents = 1299 WHERE product_id = 1 change?
06Why is DELETE FROM products WHERE product_id = 2 different from setting is_active = 0?
07What does INSERT INTO archive SELECT ... FROM drafts copy?
08If a CHECK rejects an INSERT, what should you do?
09Which DELETE is safest for a catalog cleanup?
10After a write, why run a SELECT?
PREVIOUS LESSONViews and materialized views
NEXT LESSONUpserts and conflict handling
ON THIS PAGEINSERTINSERT...SELECTPreview the WHEREUPDATEConstraints on writesDELETESoft deleteWrite checklistIndependent labCommon mistakesKnowledge check
Course contents