UPDATE

You can update a physical table view based on a single physical table. See INSERT, UPDATE, and DELETE on Views for rules on updating views.

Syntax

UPDATE <table>
SET <column> = <expression [, <column> = <expression>]*
[WHERE <criteria>]

Remarks

• If a non-nullable column is set to NULL, the data source layer throws a runtime exception.
• If the column is set to an invalid value, the data source layer throws an runtime exception.
• The WHERE clause can have a subquery.
— All database objects referenced in the subquery must be from the same data source as the target of the UPDATE.
— IN subqueries can be scalar or not.
— Depending on the relational operator, quantified subqueries may need to be scalar.
— If the subquery references incorrect rows, unexpected target rows might be affected.
• The SET clause can have a subquery.
— All database objects referenced in the subquery must be from the same data source as the target of the UPDATE.
— Subqueries of SET clauses must be scalar (that is, return one value as one row).

Example (Using UPDATE with SET)

PROCEDURE sc5()
  BEGIN
    UPDATE
      /shared/examples/ds_inventory/products
    SET
      ProductName = 'Apple';
  END

Example (Using UPDATE with SET and WHERE)

PROCEDURE sc6()
  BEGIN
     UPDATE 
     /shared/examples/ds_inventory/products 
     SET 
		ProductName = 'Lexington Z24'
     WHERE 
ProductID = 5;
  END

Example (Using UPDATE with SET and a Subquery)

PROCEDURE sc8()
  BEGIN
    UPDATE /shared/examples/ds_orders2/products 
    SET 
    ProductName = 'abc'
    WHERE 
    ProductID IN 
    (SELECT ProductID FROM     /shared/examples/ds_orders2/orderdetails);
  END