UPDATE Statement in SQL

The UPDATE statement is used to change existing data inside a table.

UPDATE Students
SET city = 'Patna'
WHERE student_id = 101;

This changes the city of the student whose ID is 101 to Patna.

UPDATE table_name
SET column_name = value
WHERE condition;

The SET clause specifies the new value, while WHERE identifies the records that should be updated.

UPDATE Students
SET city = 'Delhi'
WHERE student_id = 105;

Only the city of student 105 is changed.

You can update multiple columns in the same UPDATE statement.

UPDATE Students
SET city = 'Delhi',
    course = 'Python'
WHERE student_id = 105;

Both city and course are changed for student 105.

WHERE is used to select the records that should be modified.

UPDATE Students
SET status = 'Active'
WHERE student_id = 101;

Only the student with ID 101 is updated.

If WHERE is omitted, the UPDATE statement can modify every row in the table.

UPDATE Students
SET status = 'Active';

Text values are normally written inside quotes.

UPDATE Students
SET course = 'Java'
WHERE student_id = 110;

The course of student 110 is changed to Java.

Numeric values can be updated directly without quotes.

UPDATE Students
SET fee = 15000
WHERE student_id = 101;

The fee of student 101 is changed to 15000.

UPDATE Students
SET status = 'Active'
WHERE city = 'Patna'
AND course = 'Python';

This updates students who are from Patna and are studying Python.

UPDATE Students
SET status = 'Active'
WHERE city = 'Patna'
OR city = 'Delhi';

This updates students from either Patna or Delhi.

The IN operator can be used with UPDATE to modify records matching multiple values.

UPDATE Students
SET status = 'Active'
WHERE city IN ('Patna', 'Delhi', 'Gaya');

This updates students from the specified cities.

UPDATE Students
SET status = 'Inactive'
WHERE city NOT IN ('Patna', 'Delhi');

This updates students whose city is not Patna or Delhi.

UPDATE Students
SET category = 'Adult'
WHERE age BETWEEN 18 AND 30;

This updates students whose age is between 18 and 30.

UPDATE Students
SET status = 'Verified'
WHERE name LIKE 'A%';

This updates students whose names start with A.

You can set a column to NULL if the column allows NULL values.

UPDATE Students
SET email = NULL
WHERE student_id = 101;

This removes the stored email value by setting it to NULL.

IS NULL can be used in the WHERE condition.

UPDATE Students
SET status = 'Pending'
WHERE email IS NULL;

This updates students whose email is NULL.

UPDATE Students
SET status = 'Verified'
WHERE email IS NOT NULL;

This updates students who have an email value.

Date columns can also be updated.

UPDATE Students
SET admission_date = '2026-09-20'
WHERE student_id = 101;

This changes the admission date for student 101.

UPDATE Students
SET active = 1
WHERE student_id = 101;

If the active column is designed to use 1 for active and 0 for inactive, this marks the student as active.

You can update a numeric value using an expression based on its existing value.

UPDATE Students
SET fee = fee + 1000
WHERE course = 'Python';

This increases the fee by 1000 for Python students.

UPDATE Students
SET fee = fee - 500
WHERE course = 'Java';

This decreases the fee by 500 for Java students.

One UPDATE statement can modify multiple rows when several rows satisfy the WHERE condition.

UPDATE Students
SET status = 'Active'
WHERE course = 'Python';

All matching Python student records are updated.

UPDATE Students
SET course = 'Python',
    fee = 15000,
    status = 'Active'
WHERE city = 'Patna'
AND age BETWEEN 18 AND 30;

This updates multiple columns for students from Patna whose age is between 18 and 30.

An UPDATE statement can use a subquery when the new value or condition depends on another query.

UPDATE Students
SET fee = (
    SELECT MAX(fee)
    FROM Students
)
WHERE student_id = 101;

This sets the fee of student 101 to the maximum fee found in the Students table.

CASE can be used to assign different values based on conditions.

UPDATE Students
SET category =
CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 THEN 'Adult'
    ELSE 'Unknown'
END;

This assigns a category based on the student's age.

Before running an UPDATE, it is a good practice to run a SELECT using the same WHERE condition.

SELECT *
FROM Students
WHERE city = 'Patna'
AND course = 'Python';

After checking the returned records, you can apply the UPDATE.

UPDATE Students
SET status = 'Active'
WHERE city = 'Patna'
AND course = 'Python';

A very common mistake is forgetting the WHERE clause.

Dangerous:

UPDATE Students
SET fee = 10000;

This can change the fee of every row.

Safer:

UPDATE Students
SET fee = 10000
WHERE student_id = 101;

This changes only the specified student.

In database systems that support transactions, UPDATE operations can be used inside a transaction so that changes can be reviewed and either committed or rolled back.

START TRANSACTION;

UPDATE Students
SET fee = 15000
WHERE student_id = 101;

COMMIT;

The exact transaction behavior depends on the database engine and storage configuration.

Suppose an institute wants to increase the fee of all Python students from 10000 to 12000.

UPDATE Students
SET fee = 12000
WHERE course = 'Python'
AND fee = 10000;

This changes only Python students whose current fee is exactly 10000.

UPDATE Students
SET fee = 15000,
    status = 'Active',
    city = 'Patna'
WHERE course IN ('Python', 'Java')
AND age BETWEEN 18 AND 30
AND email IS NOT NULL;

This query:

• Updates students studying Python or Java.
• Checks that their age is between 18 and 30.
• Requires an available email value.
• Changes the fee to 15000.
• Changes the status to Active.
• Changes the city to Patna.