UPDATE

The UPDATE statement 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.

The UPDATE statement uses the following syntax:

Syntax

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

assignment:

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

In the following example, a simple UPDATE statement is used to revise the credit limit of all customers in the Customer table.

UPDATE Customer 
    SET CreditLimit = 50000;

Use the WHERE clause to identify a specific column and row to be updated, as shown in the following example.

UPDATE Customer 
       SET CreditLimit = 50000
       WHERE Name = 'World Cup Soccer';

The following example 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 following example updates the postal code to '99999' for the customer ‘1428' for all tenants.

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

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')

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

The above UPDATE statement does not change the partition of the row.

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

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

For more information on the UPDATE statement, see OpenEdge SQL Reference.