Db2 UPDATE

Summary: in this tutorial, you will learn how to use the Db2 UPDATE statement to modify data in a table.

Db2 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]
Code language: SQL (Structured Query Language) (sql)

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 condition in the WHERE clause to evaluate to true will be updated. The WHERE clause is optional, if you omit it, the UPDATE statement 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
Code language: SQL (Structured Query Language) (sql)

Db2 UPDATE examples

We’ll use the lists table created in the INSERT statement tutorial.

Here is the data of the lists table:

Db2 UPDATE lists table data

1) Using Db2 UPDATE to update a single row example

The following example uses the UPDATE statement to update a single row in the lists table:

UPDATE lists SET description = 'General topics that sent out daily' WHERE list_id = 1;
Code language: SQL (Structured Query Language) (sql)

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)
Code language: SQL (Structured Query Language) (sql)

After executing the statement, you can view the modified data in the lists table by using the following SELECT statement:

SELECT list_id, list_name, description, created_at FROM lists;
Code language: SQL (Structured Query Language) (sql)

As you can see, the value in the description has been updated successfully.

Db2 UPDATE one row example

2) Using Db2 UPDATE to update multiple rows example

The following statement updates the description of rows whose description is NULL to 'N/A':

UPDATE lists SET description = 'N/A' WHERE description IS NULL;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

9 rows updated.
Code language: SQL (Structured Query Language) (sql)

It means that 9 rows have been updated.

Db2 UPDATE multiple rows example

Now, you should know how to use the Db2 UPDATE statement to modify data in one or more rows of a table.

Was this tutorial helpful ?