Changing Data: INSERT, UPDATE, DELETE

Add new rows, fix old ones, and remove what you no longer need

Big Idea: So far you only read data with SELECT. Now you learn the three commands that change what is stored: INSERT adds new rows, UPDATE changes rows that already exist, and DELETE removes rows. Together they are the "write" side of SQL.

By the end you will be able to:

add one row or many rows at once with INSERT INTO,
change existing rows with UPDATE ... SET,
follow the golden rule: test your WHERE with a SELECT first,
remove rows with DELETE, and
explain how DELETE differs from TRUNCATE.

1. Our example table

Every example in this lesson uses one small table called students. It lives in a school database and holds a few classmates and their reward points. Here is how it was made (you learned CREATE TABLE in the last topic):

CREATE TABLE students (
    id     INT PRIMARY KEY,
    name   VARCHAR(50),
    age    INT,
    points INT
);

Right now the table is empty — it has columns but no rows. Our first job is to put some students in it. Think of the table as a fresh, empty spreadsheet with the column titles already written at the top.

2. INSERT: adding one row

To add a row you use INSERT INTO. You name the table, list the columns in brackets, then give a matching list of VALUES:

INSERT INTO students (id, name, age, points)
VALUES (1, 'Amina', 12, 100);

The order of the values must line up with the order of the columns: 1 goes into id, 'Amina' into name, 12 into age, and 100 into points. Text values go inside single quotes ('Amina'); numbers do not.

After running it, the table holds exactly one row:

id	name	age	points
1	Amina	12	100

Tip: Listing the column names is optional in MySQL, but always do it. It keeps your INSERT working even if someone later adds a new column to the table.

Changing Data: INSERT, UPDATE, DELETE