DB2 - UPDATE Statement




DB2 UPDATE Statement

To update data in a table or view, use the UPDATE statement. With the UPDATE statement, you can change the value of one or more columns in each row that meets the search condition of the WHERE clause. The result of the UPDATE statement is one or more changed column values in zero or more rows of a table (depending on how many rows meet the search condition specified in the WHERE clause). The UPDATE statement looks like this −

UPDATE table-name
     SET column-1 = value-1,
         column-2 = value-2, ...
     WHERE search-condition ...

Suppose that an employee is relocated. To update the CORPDATA.EMPLOYEE table to reflect the move, run the following statement −

UPDATE CORPDATA.EMPLOYEE
     SET JOB = :PGM-CODE,
         PHONENO = :PGM-PHONE
     WHERE EMPNO = :PGM-SERIAL

Use the SET clause to specify a new value for each column that you want to update. The SET clause names the columns that you want updated and provides the values that you want them changed to. You can specify the following types of values −

A column name. Replace the column's current value with the contents of another column in the same row.

A null value. Replace the column's current value with the null value, using the keyword NULL. The column must be defined as capable of containing a null value when the table was created, or an error occurs.

A constant. Replace the column's current value with the value provided in the SET clause.

A special register. Replace the column's current value with a special register value; for example, USER.

A host variable. Replace the column's current value with the contents of a host variable.

What is DB2 UPDATE Statement ?

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]

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		

UPDATE statement is used to update the rows which are already existed in the table. UPDATE can be single row update or mass update (more than one row) based on the condition provided in WHERE clause. User should have UPDATE privilege on the table to perform UPDATE operation. Updating row in view can update the row in base table as well. Once the UPDATE executed successfully, the rows updated count will be stored in SQLERRD.

Syntax

UPDATE table-name
SET COLUMN1 = column1-value,	 
    COLUMN2 = column2-value,	
                    .
    COLUMNn = columnn-value,	
    WHERE conditions  

UPDATE Statement

UPDATE is a DML statement that modifies rows in a table. An UPDATE statement can start with a WITH clause to define common table expressions accessible within the UPDATE. See Section 13.2.15, “WITH (Common Table Expressions)”.

Syntax

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Multiple-table syntax

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value.

The WHERE clause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order that is specified. The LIMIT clause places a limit on the number of rows that can be updated.

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. Each matching row is updated once, even if it matches the conditions multiple times. For multiple-table syntax, ORDER BY and LIMIT cannot be used. For partitioned tables, both the single-single and multiple-table forms of this statement support the use of a PARTITION option as part of a table reference. This option takes a list of one or more partitions or subpartitions (or both). Only the partitions (or subpartitions) listed are checked for matches, and a row that is not in any of these partitions or subpartitions is not updated, whether it satisfies the where_condition or not.

Note - Unlike the case when using PARTITION with an INSERT or REPLACE statement, an otherwise valid UPDATE ... PARTITION statement is considered successful even if no rows in the listed partitions (or subpartitions) match the where_condition.