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