UPDATE statement

The UPDATE statement updates the values of specified rows and columns in a table in an external database.

Syntax

Examples

The following example updates the PRICE column of the row in the STOCKPRICES table whose COMPANY column matches the value given in the Company field in the message that the Database node is processing.
UPDATE Database.StockPrices AS SP
 SET PRICE = Body.Message.StockPrice
 WHERE SP.COMPANY =Body.Message.Company
In the following example, the INV.QUANTITY in the right hand side of the assignment refers to the previous value of the column before any updates have taken place:
UPDATE Database.INVENTORY AS INV
 SET QUANTITY = INV.QUANTITY - Body.Message.QuantitySold
 WHERE INV.ITEMNUMBER = Body.Message.ItemNumber
The following example shows multiple column updates:
UPDATE Database.table AS T
 SET column1 = T.column1+1,
     column2 = T.column2+1;

The column on the left of the assignment must be a single identifier. It must not be qualified with a table name or correlation name. In contrast, any column references to the right of the assignment must be qualified with a table name or correlation name.

Compare the syntax to the way that you assign to multiple fields in a Compute node:
SET field = expression;

Handling database errors

For information about handling database errors, see Capturing database state.

Related concepts
ESQL

Related tasks
Developing ESQL
Capturing database state

Related reference
Syntax preference
ESQL statements