SQL UPDATE Command in MySQL

The SQL UPDATE command changes values in existing rows. In most practical queries, combine UPDATE with a WHERE condition so only the intended rows are changed.

UPDATE student
SET mark = 80
WHERE id = 7;

This changes mark to 80 only for the row where id=7.

SQL UPDATE Syntax Top ↑

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

The SET clause defines the new value or expression. The WHERE clause decides which rows are eligible for the change.

Update One or Selected Rows Top ↑

When a unique ID is known, it is usually the clearest way to target one row:

UPDATE student
SET class = 'Five'
WHERE id = 7;

You can also update every row matching another condition:

UPDATE student
SET class = 'Five'
WHERE class = 'Four';

This changes only records where the current class is Four.

Update All Rows Top ↑

If there is no WHERE clause, MySQL applies the update to every qualifying row in the table:

UPDATE student
SET mark = 0;

This intentionally sets mark to zero for all students.

UPDATE with AND / OR Conditions Top ↑

Multiple conditions make an update more selective. For example, promote students from class Four only when their mark is at least 70:

UPDATE student
SET class = 'Five'
WHERE class = 'Four'
  AND mark >= 70;

Use parentheses when combining AND and OR so the intended logic is explicit:

UPDATE student
SET mark = mark + 5
WHERE (class = 'Four' OR class = 'Five')
  AND mark < 70;

See SQL AND / OR conditions for condition precedence and grouping.

Update Multiple Columns Top ↑

Separate several assignments in the SET clause with commas. Using the sample student3 table:

Change all three subject marks for student ID 4:

UPDATE student3
SET math = 50,
    social = 60,
    science = 55
WHERE id = 4;

The assignments are part of one UPDATE statement.

Increase or Calculate Existing Values Top ↑

The new value can be calculated from the existing value:

UPDATE student
SET mark = mark + 5
WHERE id = 7;

For several columns:

UPDATE student3
SET math = math + 5,
    social = social + 5,
    science = science + 5
WHERE id = 3;

This increases Arnold's marks by 5 without first reading the values into an application.

Preview Rows before Running UPDATE Top ↑

For an important update, first run a SELECT using the same WHERE condition:

SELECT id, name, class, mark
FROM student
WHERE class = 'Four'
  AND mark >= 70;

After confirming the rows, use the same condition in the UPDATE:

UPDATE student
SET class = 'Five'
WHERE class = 'Four'
  AND mark >= 70;

This simple check helps catch overly broad conditions before data is changed.

Update One Table from Another Table Top ↑

MySQL can update one table using values from another related table. Suppose student3 stores three subject marks while student3_total stores the total mark.

First, a total can be calculated from the source table:

SELECT id, math + social + science AS total_mark
FROM student3;

Then update the related rows using a JOIN:

UPDATE student3_total AS t
INNER JOIN student3 AS s
    ON t.s_id = s.id
SET t.mark = s.math + s.social + s.science;

The join condition links student3_total.s_id to student3.id.

UPDATE with LEFT JOIN and INNER JOIN Top ↑

An INNER JOIN updates rows that have a matching row in both tables:

UPDATE student3_total AS t
INNER JOIN student3 AS s
    ON t.s_id = s.id
SET t.mark = s.math + s.social + s.science;

A LEFT JOIN keeps every row from the table being updated, even when the joined table has no match:

UPDATE student3_total AS t
LEFT JOIN student3 AS s
    ON t.s_id = s.id
SET t.mark = s.math + s.social + s.science
WHERE s.id IS NOT NULL;

The additional WHERE condition prevents unmatched rows from being assigned a NULL arithmetic result in this example.

See SQL LEFT JOIN for join behavior in more detail.

UPDATE using More Than Two Tables Top ↑

MySQL also allows several joins in one UPDATE. Use aliases so the table relationships remain readable:

UPDATE table1 AS a
LEFT JOIN table2 AS b
    ON a.ORD_NO = b.ORD_NO
LEFT JOIN table3 AS c
    ON c.empno = b.empno
SET a.table1_column = CONCAT(a.column1, b.table2_column3, c.name)
WHERE a.column2 > 100;

Update Stored Totals or Averages Top ↑

If a table intentionally stores a derived value, it can be recalculated with UPDATE. For example, store each student's average of three subjects:

UPDATE student3_avg
SET average = (social + math + science) / 3;

Before storing a calculated total or average, consider whether it actually needs to be stored. Values that can be calculated directly from current source columns may become inconsistent if the source data changes later.

For grouped calculations, see GROUP BY and AVG().

How Many Rows Did UPDATE Change? Top ↑

The SQL statement performs the update; the client API reports the affected-row information. With normal MySQL behavior, an UPDATE commonly reports rows whose values were actually changed.

UPDATE student
SET class = 'Five'
WHERE id = 7;

An affected-row result of zero can mean no row matched the condition, or that the matching row already contained the value being assigned. Client configuration can affect matched-versus-changed row reporting.

See MySQL affected rows and PDO rowCount() for application-side examples.

Do Not Use MD5 for Password Updates Top ↑

Older SQL examples sometimes used:

-- Do not use this for password storage
UPDATE user_mem
SET password = MD5(password);

Do not use MD5 for storing passwords. Password hashing should be handled by the application with a modern password-hashing API such as PHP password_hash() and verified with password_verify(). SQL UPDATE should store the already-created password hash as data.

Common SQL UPDATE Problems Top ↑

Forgetting the WHERE Clause Top ↑

The query can update every row. Use SELECT first when there is any uncertainty about the target condition.

Using the Wrong WHERE Condition Top ↑

A syntactically valid UPDATE can still change the wrong rows. Check IDs, ranges and AND/OR grouping before executing the update.

Using = NULL in a Condition Top ↑

NULL comparisons require IS NULL or IS NOT NULL, not ordinary equality. See SQL NULL values.

Updating a Column with an Incompatible Value Top ↑

The assigned value must be valid for the column datatype and constraints.

Building UPDATE SQL from Raw Application Input Top ↑

When values come from PHP forms or URLs, use a prepared statement rather than concatenating raw input into SQL. See PHP PDO UPDATE.

Storing Values That Could Be Calculated Top ↑

Stored totals and averages can become stale when source data changes. Store derived values only when the application has a reason to maintain them.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: