• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL UPDATE

← Previous Next →

In MySQL, the UPDATE statement is used to modify existing records in a table. It allows you to change one or more column values for selected rows. UPDATE is a critical operation in database management because data often changes over time — for example, updating a student’s city when they move or adjusting a product’s price.

Using UPDATE effectively requires understanding how to target the correct rows. Without proper filtering, you could unintentionally modify the entire table.

Basic Syntax

The simplest form of UPDATE is:

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

  • table_name is the table where the record will be updated.

  • SET column1 = value1 specifies the columns and new values.

  • WHERE condition identifies which rows to update.

Example:

UPDATE students
SET city = 'Pune'
WHERE name = 'Meera';

  • This updates the city of Meera to Pune.

  • Without the WHERE clause, all rows in the table would be updated.

Updating Multiple Columns

You can update several columns in one statement:

UPDATE students
SET city = 'Delhi', age = 21
WHERE name = 'Riya';

  • Here, both the city and age of Riya are updated in a single query.

Updating Multiple Rows

You can update multiple rows at once by using a condition that matches more than one row:

UPDATE students
SET city = 'Mumbai'
WHERE age > 20;

  • All students older than 20 now have their city updated to Mumbai.

Updating All Rows

If you omit the WHERE clause, every row is updated:

UPDATE students
SET city = 'Unknown';

  • Use this carefully, as it can overwrite all data in the column.

Using Expressions in UPDATE

You can use arithmetic expressions to modify numeric columns:

UPDATE students
SET age = age + 1
WHERE city = 'Mumbai';

  • This increases the age of all students living in Mumbai by 1 year.

You can also use string functions:

UPDATE students
SET name = CONCAT(name, ' Kumar')
WHERE city = 'Delhi';

  • This appends ' Kumar' to the names of students in Delhi.

Updating NULL Values

You can specifically update rows where a column is NULL:

UPDATE students
SET city = 'Pune'
WHERE city IS NULL;

  • This assigns a value to students whose city was previously unknown.

Using Subqueries in UPDATE

You can use subqueries to update columns based on data from another table:

UPDATE students s
SET s.city = (SELECT c.city
              FROM cities c
              WHERE c.student_id = s.id)
WHERE s.city IS NULL;

  • This updates students’ cities using information from the cities table.

ORDER BY and LIMIT in UPDATE

MySQL allows you to combine ORDER BY and LIMIT in UPDATE:

UPDATE students
SET age = age + 1
ORDER BY age ASC
LIMIT 2;

  • Only the two youngest students have their age increased.

  • This is useful for batch updates or prioritizing certain rows.

Best Practices

  1. Always use WHERE unless you intend to update all rows.

  2. Test with SELECT first to confirm which rows will be affected.

  3. Use transactions when updating multiple rows in critical tables to allow rollback.

  4. Be cautious with subqueries and JOINs to avoid updating more rows than intended.

  5. Use LIMIT and ORDER BY for controlled batch updates.

Real-Life Example

Suppose the students table contains:

id name age city
1 Aditi 19 Mumbai
2 Meera 20 Pune
3 Pooja 18 Pune
4 Riya 21 Delhi
5 Ananya 22 NULL

Example 1 – Update a single row:

UPDATE students
SET city = 'Mumbai'
WHERE name = 'Riya';

Result: Riya’s city becomes Mumbai.

Example 2 – Update multiple columns:

UPDATE students
SET age = 20, city = 'Delhi'
WHERE name = 'Pooja';

Result: Pooja’s age and city are updated.

Example 3 – Update all rows (use with caution):

UPDATE students
SET city = 'Unknown';

Result: Every student’s city becomes Unknown.

Example 4 – Update using arithmetic expressions:

UPDATE students
SET age = age + 1
WHERE city = 'Pune';

Result: Meera and Pooja’s ages are incremented by 1.

Example 5 – Update NULL values:

UPDATE students
SET city = 'Mumbai'
WHERE city IS NULL;

Result: Ananya’s city is updated from NULL to Mumbai.

Summary of the Tutorial

  • The UPDATE statement modifies existing records in a table.

  • Always include a WHERE clause unless updating all rows intentionally.

  • You can update one or multiple columns, use arithmetic expressions, string functions, or subqueries.

  • Handle NULL values and use ORDER BY/LIMIT for controlled updates.

  • Testing with SELECT before updating and using transactions ensures data safety.

Proper use of UPDATE ensures your database remains accurate and up-to-date, reflecting real-world changes in data.

Practice Questions

  1. Update the city of a student named ‘Meera’ to ‘Pune’.

  2. Change the age of a student named ‘Riya’ to 22.

  3. Update both age and city for a student named ‘Pooja’.

  4. Increase the age of all students in ‘Mumbai’ by 1.

  5. Update the city of students whose city is NULL to ‘Delhi’.

  6. Append ‘ Sharma’ to the name of students living in ‘Delhi’.

  7. Update the age of the two youngest students using ORDER BY and LIMIT.

  8. Set the city of all students to ‘Unknown’ (without WHERE clause).

  9. Use a subquery to update the city of students based on a cities table.

  10. Update students whose age is greater than 20 and city is ‘Mumbai’ to set city to ‘Pune’.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top