UPDATE

Updates the rows and columns of the specified table with the given values for rows that satisfy the search_condition.

When updating row(s) of a multi-tenant table, a regular tenant can only update rows in its partition, and the rows remain in the same tenant partition, but a super-tenant or a DBA can update rows in all the tenant partitions and group partitions. A super-tenant or a DBA may refine which tenants' rows must be affected by using the tenantid_tbl() or the tenantName_tbl() function in the WHERE clause search_condition.

Updating a row of a partitioned table may result in one of the following:

• If unpartitioned key columns are updated, then the updated new record remains in its original partition. In this case, there is no change in the behavior of the UPDATE statement.
• If only one RANGE partition key column is updated and the updated value is in the existing partition range, then the record remains in the existing partition.
• If one or more LIST partition key columns are being updated (or the RANGE partition key column is updated and the new value are not in the existing partition), then the updated new record is moved to a different partition. The new partition ID is determined with the newly updated record's partition key columns.
• If the new record's partition key columns cannot determine any partition while updating the record, then the UPDATE statement returns an error stating that the updated row does not belong to any partition.
• If the row being updated belongs to a read-only partition, then the UPDATE statement returns an error. The UPDATE statement also returns an error if the row being updated belongs to a read-write partition and the target partition is a read-only partition.

Syntax

UPDATE table_name 
  SET assignment [, assignment], ...
   [ WHERE search_condition] ;

Parameter

assignment:

The syntax for assignment is given below:

column = {expr| NULL } | (column [, column], ... ) = (expr [, expr]) | (column[, column] , ... ) = (query_expression)

Notes

• If you specify the optional WHERE clause, only rows that satisfy the search_condition are updated. If you do not specify a WHERE clause, all rows of the table are updated.
• If the expressions in the SET clause are dependent on the columns of the target table, the expressions are evaluated for each row of the table.
• If a query _expression is specified on the right-hand side of an assignment, the number of expressions in the first SELECT clause must be the same as the number of columns listed on the left-hand side of the assignment.
• If a query _expression is specified on the right-hand side of an assignment, it must return a single row.
• If a table has check constraints and if the columns to be updated are part of a check expression, then the check expression is evaluated. If the result of the evaluation is FALSE, then, the UPDATE statement fails.
• If a table has a primary or candidate key, and if the columns to be updated are a part of the primary or candidate key, SQL checks to determine if there is a corresponding row in the referencing table. If there is no corresponding row, then, the UPDATE statement fails.

Column names in the SET clause do not need a table_name qualifier. Since an UPDATE statement affects a single table, columns in the SET clause are implicitly qualified to the table name identified in the UPDATE clause.

Examples

The following examples illustrate the UPDATE statement:

The example below illustrates the different forms of UPDATE statement.

UPDATE Orderline
     SET Qty = 186
     Where Ordernum = 22;
  Update Orderline
      SET (Itemnum) = 
                      (Select Itemnum 
                       FROM Item
                       WHERE Itemname = 'Tennis balls')
     WHERE Ordernum = 20;

UPDATE Orderline
          SET (Qty) = (200 * 30)
          WHERE OrderNum = 19;
  UPDATE OrderLine
          SET (ItemNum, Price) = 
                              (SELECT ItemNum, Price * 3
                               FROM Item
                               WHERE ItemName = 'gloves') 
          WHERE OrderNum = 21 ;

the example below updates the postal code to '99999' for a customer ‘1428' for the tenant SNCSoftware.

UPDATE pub.mtcustomer
SET postalcode = '99999'
WHERE custnum = 1428 AND tenantName_tbl (pub.mtcustomer) = 'SNCSoftware';

The example below updates the postal code to '99999' for the customer ‘1428' for all tenants:

UPDATE pub.mtcustomer
SET postalcode = '99999'
WHERE custnum = 1428;

Updating partitioned tables

The following examples illustrate updating rows of a table that is partitioned by RANGE.

Assume that the table Pub.tporder is partitioned by RANGE based on the column OrderDate as given below:

• PART1_RANGE OrderDate <= ('01/01/1998')
• PART2_RANGE OrderDate <= ('01/01/2010')
• PART3_RANGE OrderDate <= ('01/01/2018')

Assume that the following rows exist in the table:

• (OrderNum, Custnum, OrderDate)
• (1, 100, '10/10/1990')
• (2, 101, '10/10/2017')

The below UPDATE statements do not change the partition of the row:

UPDATE Pub.tporder
SET OrderDate = '01/05/1997'
WHERE OrderNum = 1;

UPDATE Pub.tporder
SET Custnum = 103
WHERE OrderNum = 1;

The below UPDATE statement results in changing the partition of a row and moves the row from partition PART3_RANGE to PART1_RANGE:

UPDATE Pub.tporder
SET OrderDate = '01/05/1997'
WHERE OrderNum = 2;

The below UPDATE statement results in an error since the row does not belong to any partition:

UPDATE Pub.tporder
SET OrderDate = '01/01/2019'
WHERE OrderNum = 1;