SQL UPDATE Statement — Modifying Data in SQL Tables

One student’s mark was entered as 70 instead of 75. The office must correct that record without changing everyone’s mark.

UPDATE changes values in existing rows. WHERE determines which rows are eligible for that change.

SET describes the new values and WHERE selects the affected records. Preview the matching rows with SELECT using the same condition, then perform the change in an appropriate transaction when your workflow requires one. A key condition usually identifies the intended individual more reliably than a name.

Related concepts: Filter with WHERE and Transactions.

SQL · portable concepts; preserved examples identify their engine

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Where:

• table_name: The name of the table you want to update
• SET: Specifies which columns should be updated and their new values
• WHERE: Filters which rows to update (always use this carefully!)

SQL · portable concepts; preserved examples identify their engine

Consider that we have students table in our database.

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10),
  age INT,
  city VARCHAR(30)
);

INSERT INTO students VALUES
(1, 'Arjun Deshmukh', '10A', 15, 'Pune'),
(2, 'Priya Reddy', '9B', 14, 'Hyderabad'),
(3, 'Karan Mehta', '10A', 15, 'Ahmedabad');

Suppose Karan moved from Ahmedabad to Surat. Let’s update his city:

UPDATE students
SET city = 'Surat'
WHERE roll_no = 3;

Table Contents after UPDATE

SELECT * FROM students;

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

Let’s say Priya changed class and city:

UPDATE students
SET class = '10C', city = 'Bengaluru'
WHERE roll_no = 2;

Table Contents after UPDATE

SELECT * FROM students;

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

This is powerful — and dangerous. Without a WHERE clause, all rows are updated.

UPDATE students
SET age = age + 1;

Table Contents after UPDATE

SELECT * FROM students;

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

Update students of class 10A who live in Pune:

UPDATE students
SET city = 'Mumbai'
WHERE class = '10A' AND city = 'Pune';

WHERE condition selects all the rows with class 10A and place Pune.

Table Contents after UPDATE

SELECT * FROM students;

SELECT * FROM students
WHERE class = '10A' AND city = 'Pune';

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

SQL · portable concepts; preserved examples identify their engine

Let’s assume we have a student_marks table:

CREATE TABLE student_marks (
  roll_no INT,
  subject VARCHAR(30),
  marks INT
);

INSERT INTO student_marks VALUES
(1, 'Maths', 80),
(2, 'Maths', 85),
(3, 'Maths', 75);

Add 5 bonus marks to everyone:

UPDATE student_marks
SET marks = marks + 5;

The surrounding query explains the specific highlighted values. Highlights annotate this example; they do not change the stored table.

• Use WHERE thoughtfully — it's your safety net.
• Preview with SELECT to see what rows will change.
• Back up your data before bulk updates in production.

Leaving out WHERE can update every row. A client’s safe-update setting is an additional safeguard, not a replacement for a correct condition. Expressions use engine-specific update semantics when several assignments depend on each other.

Correct Diya’s mark to 75 by roll number and inspect the result.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

UPDATE practice_students SET marks = 75 WHERE roll_no = 2;
SELECT roll_no, name, marks FROM practice_students WHERE roll_no = 2;

The key condition restricts the change to one row. The highlighted cell in the preserved examples indicates a changed value; neighboring fields remain part of the same record.

UPDATE changes values in existing rows. WHERE determines which rows are eligible for that change.

The key condition restricts the change to one row. The highlighted cell in the preserved examples indicates a changed value; neighboring fields remain part of the same record.

Continue your understanding with Filter with WHERE and Transactions.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.