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:

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.

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.

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