Summary: in this tutorial, you will learn how to use the Db2
UPDATE statement to modify data in a table.
UPDATE statement overview
To change the existing data in a table, you use the following
UPDATE statement. Here is its syntax:
UPDATE table_name SET c1 = v1, c2 = v2, ... , cn = vn [WHERE condition]
In this syntax:
- First, specify the name of the table that you want to update data.
- Second, specify a list of column c1, c2, …, cn and the corresponding value v1, v2, … vn that need to be updated.
- Third, specify the condition to indicate which rows to be updated. Any row that causes the
WHEREclause to evaluate to true will be updated. The
WHEREclause is optional, if you omit it, the
UPDATEstatement will update all rows in the table.
You can also use the following syntax of the
UPDATE statement to update the data in a table:
UPDATE table_name SET (c1, c2, ... cn) = (v1, v2..., vn) WHERE condition
We’ll use the
lists table created in the
INSERT statement tutorial.
Here is the data of the
1) Using Db2
UPDATE to update a single row example
The following example uses the
UPDATE statement to update a single row in the
UPDATE lists SET description = 'General topics that sent out daily' WHERE list_id = 1;
In this example, we used a condition in the
WHERE clause that specifies the row whose
list_id is 1 to be updated.
Db2 issued the following message:
(1 rows affected)
After executing the statement, you can view the modified data in the
lists table by using the following
SELECT list_id, list_name, description, created_at FROM lists;
As you can see, the value in the
description has been updated successfully.
2) Using Db2
UPDATE to update multiple rows example
The following statement updates the description of rows whose description is NULL to
UPDATE lists SET description = 'N/A' WHERE description IS NULL;
Here is the output:
9 rows updated.
It means that 9 rows have been updated.
Now, you should know how to use the Db2
UPDATE statement to modify data in one or more rows of a table.