[SQL First Step] 18. Data Updating - UPDATE

UPDATE to Update a Cell

To update a cell, use UPDATE.

UPDATE table_name SET col1=value1 WHERE condition

The command will update a cell, whose row and column satisty the condition. ’=’ is an assignment operator, not a comparison operator. The value next to the ‘=’ will be a new value for the cell.

mysql> SELECT * FROM sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | ABC  | 2025-02-10 |
|  2 | XYZ  | NULL       |
+----+------+------------+
2 rows in set (0.00 sec)

mysql> UPDATE sample41 SET b='2025-02-11' WHERE no=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | ABC  | 2025-02-10 |
|  2 | XYZ  | 2025-02-11 |
+----+------+------------+
2 rows in set (0.00 sec)

If you don’t write WHERE phrase, all rows will be updated.

Advanced Skills of UPDATE

You can use the existing value as a parameter of SET. The following example uses no column to update no column.

mysql> UPDATE sample41 SET no=no+1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  2 | ABC  | 2025-02-10 |
|  3 | XYZ  | 2025-02-11 |
+----+------+------------+
2 rows in set (0.00 sec)

If you want to update multiple columns at the same time, just write all of them with commas.

UPDATE table_name SET col1=value1, col2=value2, ... WHERE condition

However, be careful of the processing order. In MySQL, unlike Oracle, the command in SET will be performed in order. Therefore, the following commands result in different database.

-- Case 1
mysql> UPDATE sample41 SET no=no+1, a=no;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  3 | 3    | 2025-02-10 |
|  4 | 4    | 2025-02-11 |
+----+------+------------+
2 rows in set (0.01 sec)

-- Case 2
mysql> UPDATE sample41 SET a=no, no=no+1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  4 | 3    | 2025-02-10 |
|  5 | 4    | 2025-02-11 |
+----+------+------------+
2 rows in set (0.00 sec)

In Oracle, SET always refers to the previous step before the command performed. Therefore, the order of columns in SET will not affect the result.

To update a cell with NULL, do as a following example.

mysql> UPDATE sample41 SET a=NULL;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  4 | NULL | 2025-02-10 |
|  5 | NULL | 2025-02-11 |
+----+------+------------+
2 rows in set (0.00 sec)

All images, except those with separate source indications, are excerpted from lecture materials.