SQL UPDATE Statement

4
0

Introduction

Stored information changes over time. A student’s marks may need correction, a customer may change their address, or a product’s price may increase.

The SQL UPDATE statement modifies existing records. It specifies what should change and, through a condition, which rows should receive the change.

What Is UPDATE in SQL?

UPDATE is a Data Manipulation Language (DML) statement. It changes column values in existing rows without adding columns or creating new records.

The basic syntax is:

UPDATE table_name
SET column_name = new_value
WHERE condition;

Each part has a separate role:

  • UPDATE: Identifies the table.

  • SET: Defines the value assignments.

  • WHERE: Selects eligible rows.

The examples below use MySQL :

student_id

student_name

department

marks

101

Asha

CSE

75

102

Ravi

ECE

72

103

Meera

CSE

91

104

Kabir

ME

NULL

Unless stated otherwise, each example starts from this original data.

Updating a Single Record

Correct Asha’s marks using her unique identifier:

UPDATE students
SET marks = 85
WHERE student_id = 101;

The resulting record is:

student_id

student_name

department

marks

101

Asha

CSE

85

Only the assigned column changes. Asha’s name, department, and identifier remain the same.

A condition using a primary key is useful when the requirement targets one specific record. Filtering by a name alone may match several students if names repeat.

If no row satisfies the condition, UPDATE does not create one. That behavior differs from an insert or an UPSERT.

UPDATE : Change one record

UPDATE : Change one record

Updating Multiple Columns

Separate assignments with commas to change several columns:

UPDATE students
SET department = 'CSE',
marks = 80
WHERE student_id = 102;

Ravi’s department becomes CSE and his marks become 80.

Use commas between assignments, not AND. Logical operators belong inside conditions when combining requirements.

Assignments can also use NULL where permitted:

UPDATE students
SET marks = NULL
WHERE student_id = 101;

This records a missing mark. It is different from storing zero or the text 'NULL'.

Updating Several Rows with a Condition

A condition can deliberately match multiple records. For example, add five marks to students currently below 80:

UPDATE students
SET marks = marks + 5
WHERE marks < 80;

Starting from the original table:

Student

Original marks

Result

Asha

75

80

Ravi

72

77

Meera

91

Unchanged

Kabir

NULL

Unchanged

The assignment uses each matching row’s stored value. Kabir does not qualify because comparing a missing mark with 80 produces unknown.

The database still enforces constraints. If an update would violate a check, unique key, or foreign key, it can fail.

Conditional Updates with CASE

Use CASE when different rows need different assigned values.

For example, assign departments according to student identifiers:

UPDATE students
SET department = CASE
WHEN student_id = 101 THEN 'ECE'
WHEN student_id = 102 THEN 'CSE'
ELSE department
END
WHERE student_id IN (101, 102);

Asha receives ECE and Ravi receives CSE. The WHERE clause limits eligible rows, while CASE determines the value assigned.

ELSE department preserves the existing value if no listed branch matches. Without an ELSE, an unmatched CASE expression returns NULL, which may be unintended or prohibited.

UPDATE with a condition  and calculation

UPDATE with a condition and calculation

The Importance of WHERE

Without WHERE, every row is eligible for the assignment:

UPDATE students
SET department = 'CSE';

This sets every student’s department to CSE, assuming the statement succeeds. Such an operation can be intentional, but it should reflect the requirement.

Before applying a targeted change, inspect its condition:

SELECT student_id, student_name, marks
FROM students
WHERE student_id = 101;

Then use that same condition in the update. This helps check the intended scope, although a separate preview does not prevent concurrent sessions from changing data.

Afterward, query the relevant records to verify their values.

Transactions and Rollback

With MySQL , an uncommitted update can be discarded within a transaction:

START TRANSACTION;
UPDATE students
SET marks = 85
WHERE student_id = 101;
SELECT student_id, marks
FROM students
WHERE student_id = 101;
ROLLBACK;

The session can inspect the changed value before rolling back. After this rollback, Asha’s marks return to 75.

Use COMMIT instead of ROLLBACK when the transaction’s changes should be retained. These are alternative decisions, not consecutive undo-and-save steps.

MySQL normally enables autocommit. A successful standalone update is therefore usually committed automatically, and a later rollback cannot undo it.

Common Mistakes

Check these points before relying on an update:

  • Missing or broad filters: Confirm whether the condition targets one row, several rows, or the entire table.

  • Confusing UPDATE with ALTER: UPDATE changes stored values; ALTER TABLE changes structure.

  • Using equality for missing-value tests: Write WHERE marks IS NULL, not WHERE marks = NULL.

  • Expecting arithmetic to fill NULL: NULL + 5 remains NULL; use an explicit assignment when replacing missing marks.

  • Relying on assignment order across systems: Expressions involving several updated columns can behave differently by database.

  • Assuming matched rows always changed: Assigning an existing value may leave it unchanged, and affected-row reporting varies by client settings.

Use suitable data types and assignments that preserve the meaning of each column.

Summary

UPDATE modifies existing records through assignments in SET and conditions in WHERE. It supports single-row corrections, multi-column changes, calculations, and conditional values.

Verify the eligible records and account for constraints and missing values. Use transactions when changes need inspection before committing, and remember that committed updates cannot be reversed by an ordinary rollback.

CS Core

Read Similar Blogs

Comments0