Skip to content

Modifying Data

Update existing rows and delete them safely.

Once data is in a table, you will need to change it. UPDATE modifies existing rows. DELETE removes them. Both require a WHERE clause or you will affect every row.

Definition

DML (Data Manipulation Language) statements modify data in existing tables. The three core DML statements are INSERT (add rows), UPDATE (modify rows), and DELETE (remove rows).

Required step: If you do not have the school database and students table yet, run this setup in the playground:

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100)
);

INSERT INTO students (name, email)
VALUES
  ('Alice', '[email protected]'),
  ('Bob', '[email protected]'),
  ('Charlie', '[email protected]');

Troubleshooting: If you get "table doesn't exist" errors, re-run the setup above. It uses IF NOT EXISTS so it is safe to run multiple times.

SQL Statement Categories:DDL vs DML

  • DDL (Data Definition Language): defines or modifies the structure of tables and databases. Examples: CREATE, ALTER, DROP, TRUNCATE.
  • DML (Data Manipulation Language): reads and modifies the data inside tables. Examples: SELECT, INSERT, UPDATE, DELETE.

TRUNCATE is classified as DDL, not DML. It drops and re-creates the table, which is why it is much faster than DELETE.

Safety Rule

Always use a WHERE clause with UPDATE and DELETE. Running them without WHERE affects every row in the table. Without transactions, there is no undo.

UPDATE

UPDATE is a DML (Data Manipulation Language) statement that modifies rows in a table. The SET clause indicates which columns to modify and the values they should be given.

Fix a student's email

UPDATE students
SET email = '[email protected]'
WHERE name = 'Alice';

Only Alice's row is changed. The WHERE clause is critical. Without it, every student's email would be overwritten.

Update multiple columns

UPDATE students
SET name = 'Alicia', email = '[email protected]'
WHERE id = 1;

You can change multiple columns in one SET clause, separated by commas.

DELETE

DELETE is a DML statement that removes rows from a table. Use the ROW_COUNT() function to see how many rows were deleted.

Remove a student

DELETE FROM students
WHERE name = 'Bob';

Only Bob's row is deleted. Always preview with SELECT first to make sure your WHERE clause is correct.

Preview before deleting

SELECT * FROM students
WHERE name = 'Bob';

DELETE FROM students
WHERE name = 'Bob';

Run the SELECT first. Check the results. Then run the DELETE with the same WHERE clause.

TRUNCATE

TRUNCATE TABLE empties a table completely. It is classified as DDL (Data Definition Language), not DML. It drops and re-creates the table, which is much faster than deleting rows one by one.

Clear the whole table

TRUNCATE TABLE students;

TRUNCATE causes an implicit commit and cannot be rolled back. It does not invoke ON DELETE triggers. Any AUTO_INCREMENT value is reset to its start value. If there are FOREIGN KEY constraints from other tables referencing this table, TRUNCATE fails.

Warning: TRUNCATE permanently deletes all rows and resets auto-increment. Later tutorials depend on this data. If you run TRUNCATE, re-insert the data from Stage 3 before continuing.

What happens if you run UPDATE students SET name = 'X'; without a WHERE clause?

What is the safest way to delete specific rows?

Key Takeaways

  • UPDATE modifies rows. SET specifies the new values.
  • DELETE removes rows. Without a transaction, there is no undo.
  • Always use WHERE to target specific rows.
  • Preview with SELECT before running DELETE.
  • TRUNCATE clears all rows at once and resets AUTO_INCREMENT.

Ready to test your knowledge?

Take a Quiz

Related Tutorials