Databases & SQL · Class 12 · Inserting & Changing Data

MySQLDML⏱️ 9 min read

Updating Rows

Data goes out of date. A student moves city, a mark is corrected, a teacher is promoted. update changes values in rows that are already there — and it comes with the most expensive single-word mistake in this whole course.

1The syntax

update <table name>
   set <column> = <value>
 where <condition>;

Three parts, and each answers a different question:

update students

Which table?

set city = 'Namchi'

What is the new value?

where id = 4001

Which rows?

The examples below run against a small four-row copy of the students table, so that every change is easy to follow:

MySQL command line client

mysql> select * from students;

+------+-------+-------+---------+------------+---------+

+------+-------+-------+---------+------------+---------+

| 1099 | Veena | 10 | 90.5 | 2010-11-20 | Singtam |

| 2035 | Ravi | 12 | 83.75 | 2008-08-13 | Gangtok |

| 3033 | Tom | 8 | 64.25 | 2013-02-25 | Kolkata |

+------+-------+-------+---------+------------+---------+

4 rows in set (0.00 sec)

2Changing one value in one row

Asha's city was never recorded. Fill it in:

MySQL command line client

mysql> update students set city = "Namchi" where id = 4001;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from students where id = 4001;

+------+------+-------+---------+------+--------+

+------+------+-------+---------+------+--------+

+------+------+-------+---------+------+--------+

1 row in set (0.00 sec)

Rows matched vs Changed

Two different counts, and the difference matters. Rows matched is how many rows the where found. Changed is how many actually ended up different. A row that already held the new value is matched but not changed.

3Changing several columns at once

One set, then as many column = value pairs as you like, separated by commas. There is no second set:

MySQL command line client

mysql> update students set percent = 71.5, grade = 12 where name = "Asha";

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from students where name = "Asha";

+------+------+-------+---------+------+--------+

+------+------+-------+---------+------+--------+

| 4001 | Asha | 12 | 71.5 | NULL | Namchi |

+------+------+-------+---------+------+--------+

1 row in set (0.00 sec)

Both columns changed, one row was touched, and it took one trip to the server.

4Forgetting the where

The where clause is optional. That is the problem. Leave it out and the update is applied to every row in the table:

MySQL command line client

mysql> update students set city = "Singtam";

Query OK, 3 rows affected (0.03 sec)

Rows matched: 4 Changed: 3 Warnings: 0

mysql> select id, name, city from students;

+------+-------+---------+

| id | name | city |

+------+-------+---------+

| 1099 | Veena | Singtam |

| 2035 | Ravi | Singtam |

| 3033 | Tom | Singtam |

| 4001 | Asha | Singtam |

+------+-------+---------+

4 rows in set (0.00 sec)

Ravi was from Gangtok, Tom from Kolkata, Asha from Namchi. All three are now from Singtam, and the original values are gone for good. There was no warning and no confirmation — the query was perfectly valid, it just did not mean what was intended.

Read the counts once more: Rows matched: 4 but Changed: 3. All four rows were selected, but Veena was already from Singtam, so hers did not actually change. That is the distinction from section 2, visible in real output.

The habit that prevents this

Before running an update, run the same where as a select first:
select * from students where id = 4001;
Whatever rows come back are exactly the rows your update will change. If that is twelve rows and you expected one, you have just saved yourself.

5When the where matches nothing

No error — the query worked, it simply found no rows to work on:

MySQL command line client

mysql> update students set city='X' where id=99999;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0 Changed: 0 Warnings: 0

Rows matched: 0 is the tell. If you expected to change something and see this, the condition is wrong — a mistyped id, or the wrong quotes around a value — not the set.