DB2 - SQL Update Statement

The UPDATE statement updates the values of specified columns in the rows of a table.

It is used to modify the existing records in a table.

UPDATE table_name
   SET column1 = value1, column2 = value2, ...
  WHERE condition;

Be careful when you updating a records in a table. Have you noticed the WHERE clause in the UPDATE statement? The WHERE clause specifies which records that should be updated. If you omit the WHERE clause, all records in the table will be updated.

DB2 Database:

Below is a selection from the "Product" table in the DB2 database.

Example 1: Updating single row

The following SQL statement updates the first ProductID (ProductID = 7001) with a new ProductName and QtyOnHand.

UPDATE Product
   SET ProductName = 'Pendrive', QtyOnHand = 30
 WHERE ProductID = 7001;

Result:

Example 2: Updating multiple rows

The following SQL statement will update the ProductName to 'Pendrive' for all records where Category is 'Accessories'.

UPDATE Product
   SET ProductName = 'Pendrive'
 WHERE Category = 'Accessories';

Result:

Tips:

If you omit the WHERE clause, ALL records will be updated. let us see an example below,

The below SQL query is same as example 2, but we ommitted WHERE clause.

UPDATE Product
  SET ProductName = 'Pendrive'

Result:

ALL records will be updated with ProductName is 'Pendrive'. So be careful when updating records.

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!