Modify Data

So far you've only read data with SELECT . By the end of this lesson you'll be able to change it — adding new rows with INSERT , editing rows with UPDATE , and removing rows with DELETE — and you'll know the one safety rule that separates a confident developer from a costly accident.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Every command in this lesson modifies this same products table. This is its starting state — picture it changing as each example runs. (Each example assumes you start from this fresh table.)

INSERT INTO adds a brand-new row to a table. You give it the values for the new row, and it appends it to the bottom. Unlike SELECT (which only reads), INSERT actually changes what's stored.

INSERT is like filling out a new form and dropping it into a filing cabinet. You decide which fields to fill in ( product_name , price , …) and what to write in each one; the cabinet just gains one more sheet.

There are two shapes. The short one leaves out the column names, so your VALUES must cover every column in the table's exact order:

The safer, more professional shape lists the columns first. Now the values line up with your list rather than the table's hidden ordering — so the statement keeps working even if the table changes later.

Fill in the four missing values to add a Gel Pen . They must line up with the column list, in order. The expected result is in the comments so you can check yourself.

UPDATE edits rows that are already in the table. It has three parts: the table to change, a SET clause naming the column(s) and their new value(s), and a WHERE clause that decides which rows are affected. The WHERE is the part you must never forget.

This first example lowers the price of a single product — the one whose id is 3:

You can change several columns at once by separating them with commas in the SET clause. Here both the price and the stock of id 2 change in a single statement:

Fill in the new price and the id . Notice how the WHERE id = … line is what stops this from repricing the whole table — that's the habit to build.

This is the most important paragraph in the lesson. UPDATE and DELETE apply to every row that matches WHERE . If you leave WHERE off, every row matches — so the change hits the entire table at once. There is no "are you sure?" pop-up.

Read the example below carefully — it is what a mistake looks like, so you can recognise and avoid it:

DELETE FROM removes whole rows that match the WHERE clause. It deletes entire rows , not single values — if you only want to clear one field, use UPDATE to set it to a new value instead.

Think of DELETE … WHERE id = 4 as pulling exactly one sheet out of the filing cabinet and shredding it. DELETE FROM products; with no WHERE shreds the whole drawer .

A transaction wraps one or more changes so they don't become permanent until you say so. After BEGIN TRANSACTION , your INSERT / UPDATE / DELETE statements are only pending . COMMIT saves them for good; ROLLBACK throws them all away as if they never happened. It's the closest thing SQL has to an undo button — and your safety net for risky changes.

Because the example ends in ROLLBACK , the table is left exactly as it started — all 6 rows still there. Swap ROLLBACK for COMMIT to make the deletion stick.

Q: What if I run an UPDATE or DELETE without a WHERE by accident?

Every row is affected. If you were inside a transaction ( BEGIN TRANSACTION ) you can ROLLBACK to undo it. If you'd already committed — or never started a transaction — you'll need a backup to recover. That's exactly why you preview with SELECT first.

Q: Do I have to list every column in an INSERT?

Only if you leave the column list out — then you must supply all of them in order. If you name the columns, you can fill just those (others get their default value or NULL ). Naming columns is the safer habit.

Q: What's the difference between UPDATE and DELETE?

UPDATE keeps the row but changes a value inside it; DELETE removes the entire row. To "clear" one field, UPDATE it to NULL or a new value — don't DELETE the whole row.

Q: Does INSERT/UPDATE/DELETE change the data permanently?

Yes — these write to the table, unlike SELECT . Outside a transaction the change is immediate and permanent. Inside one, it's pending until you COMMIT , and reversible with ROLLBACK .

Put it together — a brief, a blank canvas, and the expected before/after in the comments. The trick is the WHERE : it must limit the change to Electronics only. Write it, then copy it into a playground to confirm.

Practice quiz

Which statement adds a brand-new row to a table?

  • UPDATE
  • DELETE
  • INSERT
  • SELECT

Answer: INSERT. INSERT INTO ... VALUES adds a new row. SELECT only reads; UPDATE edits existing rows; DELETE removes rows.

Why is listing the columns in an INSERT the safer habit?

  • The values line up with your column list, so it keeps working if the table changes later
  • It makes the insert run faster
  • It allows inserting more than one row
  • It is required by all databases

Answer: The values line up with your column list, so it keeps working if the table changes later. Naming the columns ties VALUES to your list rather than the table's hidden order, so the statement still works if a column is added or reordered.

Which clause of an UPDATE decides which rows are affected?

  • SET
  • VALUES
  • FROM
  • WHERE

Answer: WHERE. SET names the columns and new values; WHERE picks which rows are changed.

What happens if you run UPDATE products SET price = 0; with no WHERE clause?

  • It updates only the first row
  • It sets the price of every row in the table to 0
  • It raises an error and changes nothing
  • It updates a random row

Answer: It sets the price of every row in the table to 0. Without a WHERE clause every row matches, so the change hits the entire table. There is no 'are you sure?' prompt.

What does DELETE FROM products WHERE id = 4; do?

  • Removes the entire row whose id is 4
  • Clears only the id field of row 4
  • Removes every row in the table
  • Sets row 4's values to NULL

Answer: Removes the entire row whose id is 4. DELETE removes whole rows that match the WHERE clause. To clear a single field, use UPDATE instead.

Inside a transaction, what does ROLLBACK do to your pending changes?

  • Saves them permanently
  • Applies them to a backup copy
  • Throws them all away as if they never happened
  • Converts them into a SELECT

Answer: Throws them all away as if they never happened. After BEGIN TRANSACTION, changes are pending. COMMIT saves them; ROLLBACK discards them all — SQL's closest thing to an undo button.

What is the recommended way to preview which rows an UPDATE or DELETE will affect?

  • Run the UPDATE first and check the result
  • Run the same filter as a SELECT first
  • Disable the WHERE clause temporarily
  • Count the rows in the whole table

Answer: Run the same filter as a SELECT first. Run your filter as a SELECT (e.g. SELECT * FROM products WHERE id = 3;) to see exactly which rows you're about to change, then swap in UPDATE or DELETE.

How do you change several columns in one UPDATE statement?

  • Use one UPDATE per column
  • List the columns in the WHERE clause
  • Use a JOIN
  • Separate the column = value pairs with commas in the SET clause

Answer: Separate the column = value pairs with commas in the SET clause. You can set multiple columns at once by separating them with commas, e.g. SET price = 8.75, stock = 350 WHERE id = 2.

A column-less INSERT (INSERT INTO products VALUES ...) requires what?

  • Only the primary key value
  • A value for every column, in the table's exact order
  • A WHERE clause
  • The columns to be listed afterward

Answer: A value for every column, in the table's exact order. Leaving out the column list means you must supply a value for every column in the table's order, or you'll get a column-count error.

Which command writes changes permanently (unlike SELECT)?

  • Only SELECT
  • Only EXPLAIN
  • INSERT, UPDATE, and DELETE
  • None — all SQL is read-only

Answer: INSERT, UPDATE, and DELETE. INSERT, UPDATE, and DELETE all write to the table. Outside a transaction the change is immediate and permanent.