MySQL UPDATE

The UPDATE statement changes existing data in a table.

UPDATE students
SET fee = 15000
WHERE id = 1;

This changes the fee of the student whose ID is 1.

The basic syntax is:

UPDATE table_name
SET column_name = value
WHERE condition;

The SET clause specifies the new value, while WHERE identifies the rows to modify.

You can update a single column.

UPDATE students
SET fee = 20000
WHERE id = 5;

Only the fee of student ID 5 is changed.

You can update more than one column in the same statement.

UPDATE students
SET fee = 18000,
    course = 'Python'
WHERE id = 5;

Both fee and course are changed for the selected student.

WHERE is used to identify specific records.

UPDATE students
SET fee = 15000
WHERE course = 'Python';

This updates all students whose course is Python.

If you omit WHERE, every row may be updated.

UPDATE students
SET fee = 10000;

This sets the fee of all students to 10000.

Using a unique ID is one of the safest ways to update a single record.

UPDATE students
SET name = 'Amit Kumar'
WHERE id = 10;

Only the student with ID 10 is updated.

Text values should be placed inside quotes.

UPDATE students
SET course = 'Java'
WHERE id = 3;

The course of student ID 3 becomes Java.

Numeric values are normally written without quotes.

UPDATE students
SET age = 25
WHERE id = 3;

The student's age is changed to 25.

Decimal columns can also be updated.

UPDATE students
SET fee = 18500.50
WHERE id = 3;

The fee is changed to 18500.50.

Comparison operators can be used in the WHERE condition.

UPDATE students
SET fee = fee + 1000
WHERE age > 20;

This increases the fee for students older than 20.

Multiple conditions can be combined using AND.

UPDATE students
SET fee = fee + 1000
WHERE course = 'Python'
AND age >= 18;

Only Python students aged 18 or above are updated.

OR can be used when either condition should match.

UPDATE students
SET fee = fee + 500
WHERE course = 'Python'
OR course = 'Java';

This updates students from Python or Java.

IN is useful when several values should be updated.

UPDATE students
SET fee = fee + 1000
WHERE course IN ('Python', 'Java', 'PHP');

This increases the fee for students in the listed courses.

LIKE can be used when records need to be selected based on a text pattern.

UPDATE students
SET course = 'Python'
WHERE name LIKE 'A%';

This changes the course for students whose names start with A.

BETWEEN can be used to update records within a range.

UPDATE students
SET fee = fee + 500
WHERE age BETWEEN 18 AND 25;

This increases the fee for students between the specified ages.

IS NULL can be used to find records with missing values.

UPDATE students
SET email = 'notprovided@example.com'
WHERE email IS NULL;

This replaces NULL email values with the specified value.

A nullable column can be changed to NULL.

UPDATE students
SET mobile = NULL
WHERE id = 7;

The mobile value of student ID 7 becomes NULL.

You can calculate a new value from the existing value.

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

The existing fee is increased by 1000 instead of replacing it with a fixed value.

UPDATE students
SET fee = fee + 2000
WHERE course IN ('Python', 'Java')
AND age >= 20
AND fee < 20000;

This updates only records that satisfy all the specified conditions.

MySQL allows LIMIT to restrict the number of rows affected by an UPDATE.

UPDATE students
SET fee = fee + 500
WHERE course = 'Python'
LIMIT 5;

This updates up to five matching records.

An UPDATE statement can change many rows when multiple rows satisfy the WHERE condition.

UPDATE students
SET course = 'Python'
WHERE course = 'Python Full Stack';

Every matching record is updated.

An AUTO_INCREMENT primary key normally does not need to be manually changed.

UPDATE students
SET name = 'Rahul Kumar'
WHERE id = 15;

Here, the ID is only used to identify the record.

Aliases can be useful in more advanced UPDATE queries.

UPDATE students AS s
SET s.fee = s.fee + 500
WHERE s.course = 'Python';

The alias s represents the students table.

MySQL can update records using information from another table with a JOIN.

UPDATE students AS s
JOIN courses AS c
ON s.course = c.course_name
SET s.fee = c.fee
WHERE c.course_name = 'Python';

This can update student fees using values from the courses table.

• Forgetting the WHERE clause
• Using the wrong WHERE condition
• Updating more rows than intended
• Forgetting quotes around text values
• Using the wrong column name
• Changing a primary key unnecessarily
• Not checking the records before updating

A safe UPDATE workflow is:

1. Write the WHERE condition.
2. Test the condition with SELECT.
3. Check the records returned.
4. Write the UPDATE statement.
5. Execute the UPDATE.
6. Run SELECT again to verify the result.

SELECT *
FROM students
WHERE id = 5;

UPDATE students
SET fee = 18000
WHERE id = 5;

SELECT *
FROM students
WHERE id = 5;

Suppose the fee of a Python student needs to be increased by ₹1,000.

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

This increases the existing fee rather than replacing it with a fixed amount.

Suppose we want to update the course and fee of a particular student.

UPDATE students
SET course = 'Python',
    fee = 20000
WHERE id = 10;

Only student ID 10 is modified.

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    course VARCHAR(100),
    age INT,
    email VARCHAR(150),
    fee DECIMAL(10,2)
);

INSERT INTO students
(name, course, age, email, fee)
VALUES
('Amit', 'Python', 22, 'amit@gmail.com', 15000.00),
('Priya', 'Java', 21, 'priya@gmail.com', 18000.00),
('Rahul', 'PHP', 24, 'rahul@gmail.com', 12000.00),
('Neha', 'Python', 20, NULL, 15000.00);

SELECT *
FROM students
WHERE course = 'Python';

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

SELECT *
FROM students
WHERE course = 'Python';

First, the Python students are displayed. Then their fees are increased by 1000. Finally, SELECT verifies the updated records.