Lesson 14 of 20

In progress

Course progress

SQL

INSERT, UPDATE and DELETE

Add rows, change them safely and remove them on purpose, with the checks that stop a missing WHERE from emptying a table.

Reading queries are safe to run twice. Write statements are not, and that asymmetry is why they deserve more care than a SELECT.

What you will learn

  • Insert single rows and many rows at once.
  • Update with a checked predicate.
  • Delete without losing data you meant to keep.

INSERT with named columns

SQL
Editable. Runs in your browser.





Press Run to see the output.

Naming the columns is not optional in practice. INSERT INTO tasks VALUES (...) depends on the column order, so adding a column to the table breaks every such statement. The named form keeps working and lets defaults apply to the columns you omit.

id is omitted and filled by the database because it is the primary key, and the defaults supply done and created_on.

Inserting several rows

SQL
Editable. Runs in your browser.





Press Run to see the output.

One statement, three rows. It is faster than three statements and atomic in most databases: either all rows are inserted or none. That matters when an import fails halfway.

For copying rows from another table, use INSERT INTO ... SELECT:

SQL
Editable. Runs in your browser.





Press Run to see the output.

UPDATE

SQL
Editable. Runs in your browser.





Press Run to see the output.

SET names the columns and their new values; WHERE chooses the rows. Without WHERE, every row is updated — the statement succeeds, and the damage is silent.

The safe routine:

  1. Run the predicate as a SELECT and check the row count and the sample.
  2. Run the UPDATE with the same predicate.
  3. Verify with a SELECT afterwards, or check the reported row count.
SQL
Editable. Runs in your browser.





Press Run to see the output.

Several columns can be set in one statement, and the right-hand side can reference existing values:

SQL
Editable. Runs in your browser.





Press Run to see the output.

In standard SQL, the assignments in a single UPDATE all read the old values. Do not assume left-to-right evaluation.

UPDATE with a join

SQLite supports UPDATE ... FROM, which lets a value come from another table:

SQL
Editable. Runs in your browser.





Press Run to see the output.

The subquery form works everywhere and reads clearly. Always check the subquery on its own first, because a wrong subquery updates the wrong rows just as quietly.

DELETE

SQL
Editable. Runs in your browser.





Press Run to see the output.

The same rule applies: no WHERE, no rows left. A DELETE inside a transaction is recoverable with ROLLBACK; outside one, it is not.

SQL
Editable. Runs in your browser.





Press Run to see the output.

Running the delete in a transaction, checking the count, then committing or rolling back is the habit that turns a mistake into a shrug.

For large tables, deleting everything row by row is slow. TRUNCATE in some databases removes all rows quickly; SQLite offers DELETE FROM table without a WHERE or DROP TABLE plus recreation. Either way, confirm there is a backup.

Reading the affected count

Most clients report how many rows a statement changed. Compare that number with the count your SELECT predicted before running the statement, and treat a mismatch as a stop sign: it means the predicate matched a different population than you tested against, and the next statement will do the same.

Return values

Most databases can return the inserted or changed rows. SQLite supports RETURNING:

SQL
Editable. Runs in your browser.





Press Run to see the output.

That saves a second query to find the generated id, which is otherwise the usual source of a race in concurrent code.

Transactions around a change

A write that touches several tables belongs in one transaction:

SQL
Editable. Runs in your browser.





Press Run to see the output.

If the second statement fails, ROLLBACK leaves the first undone. Without the transaction, the database keeps the successful half and the data no longer tells a coherent story.

Running DELETE FROM tasks while developing with the intention of "just clearing the test rows". Confirm the connection and the database name before a statement without WHERE; that is the one that ends up on production.

Archive every completed task into the archive table, then delete the archived rows from tasks, all inside a transaction. Verify the counts before committing.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

The counts before the delete should match the archived rows, and the remaining count should be the previous total minus the deleted rows. ROLLBACK instead of COMMIT reverses everything if a number is wrong.

Recap

  • Name the columns in INSERT; insert many rows in one statement for atomicity.
  • Run the WHERE predicate as a SELECT before any UPDATE or DELETE.
  • Wrap multi-statement changes in a transaction and verify before committing.

Next, you will create the tables these statements write to, with types and defaults chosen deliberately.

What does INSERT OR IGNORE do?
SQLite skips rows that violate a constraint instead of failing the statement. Useful for idempotent imports, risky if it hides real errors.
Can I undo a DELETE?
Only inside an open transaction, with `ROLLBACK`, or from a backup. Otherwise the rows are gone.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.