VisaSpark
Sign in
beginner26 min

Changing Data: INSERT, UPDATE, and DELETE

Adding new rows, modifying existing ones, and removing rows you no longer need.

What you'll learn

  • Add new rows to a table using INSERT
  • Modify existing rows using UPDATE, scoped with WHERE
  • Remove rows using DELETE, scoped with WHERE

Prerequisites

Explanation

SELECT only reads data. Three other statements actually change what's stored:

INSERT adds a new row. You name the table, list which columns you're providing, and supply matching values:

INSERT INTO books (id, title, author_id, genre, price, published_year, in_stock)
VALUES (11, 'The Left Hand of Darkness', 3, 'Fiction', 17.25, 1969, 6);

Column order in the parentheses must match the value order — the database matches them positionally, not by column name.

UPDATE modifies existing rows, and it is almost always paired with WHERE:

UPDATE books SET price = 18.99 WHERE id = 8;

This is the single most important habit in this lesson: an UPDATE (or DELETE) without a WHERE clause applies to every row in the table. UPDATE books SET price = 0; with no WHERE would zero out every book's price, not just one. Always ask "which rows am I targeting?" before running either statement, and double check your WHERE condition actually matches only the rows you intend.

DELETE removes rows, following the same pattern:

DELETE FROM orders WHERE id = 5;

Again, DELETE without a WHERE clause empties the entire table.

Because INSERT, UPDATE, and DELETE don't return rows the way SELECT does, the exercises in this lesson ask you to follow up each change with a SELECT that shows the result — proving to yourself (and to anyone checking your work) exactly what changed. That combination — modify, then verify with a SELECT — is a habit that generalizes well beyond this lesson: after any change to data, it's worth confirming the database now reflects what you expect.

Example

A new shipment arrives — increase Compiling the Future's stock count by 10, then confirm the new value.

UPDATE books
SET in_stock = in_stock + 10
WHERE id = 2;

SELECT title, in_stock FROM books WHERE id = 2;

Try it yourself

Try changing the id or the quantity added, then press Run to see the updated stock count.

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Guided exercise

Guided exercise

Insert a new book: id 11, title 'The Left Hand of Darkness', author_id 3, genre 'Fiction', price 17.25, published_year 1969, in_stock 6. Then write a SELECT that returns its title and price to confirm it was added.

Checks: Confirms the new book was inserted with the correct title and price

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Independent exercise

Independent exercise

Sam accidentally ordered '1Q84' three times (order id 5). Delete that order, then write a SELECT returning the id and customer_name of every remaining order, ordered by id ascending, to confirm it's gone (order matters for this check).

Checks: Order id 5 is deleted and the remaining orders are listed in ascending id order

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Common mistakes

  • Running UPDATE or DELETE without a WHERE clause, accidentally applying the change to every row in the table.
  • Listing INSERT columns and values in mismatched order — values are matched to columns positionally, not by name.
  • Forgetting to quote text values in an INSERT, such as writing a genre without surrounding single quotes.
  • Not verifying the result — running a data-changing statement and assuming it worked without a follow-up SELECT.

Knowledge check

Knowledge check

1. What happens if you run `UPDATE books SET price = 0;` with no WHERE clause?
2. In an INSERT statement, how are listed values matched to listed columns?
3. Which statement permanently removes rows from a table?

Takeaway

INSERT adds rows, UPDATE and DELETE change or remove existing ones — and both of the latter should almost always be scoped with a WHERE clause you've double-checked.

Summary

INSERT adds new rows by matching values to columns positionally. UPDATE and DELETE modify or remove existing rows, and without a WHERE clause they act on the entire table, making that clause the most important habit to get right.

References

Your notes

Notes save automatically.

Finished this lesson?

Mark it complete to track your progress and schedule a future review.

Next: Aggregating and Combining Data: GROUP BY and JOIN →