SQLServer Update

Use this activity to execute an update statement on one or more rows of an Microsoft SQL Server table.

Configuration

The Configuration tab has the following fields.

Field Description
Connection Name of the Microsoft SQL Server database connection from which to retrieve information. You can select a connection from the drop-down list.

Input Settings

The Input Settings tab has the following fields:

Field Description
Update Statement An SQL statement used to update one or more rows in a table. You can construct prepared SQL queries by using substitution parameters of the form ?<fieldname> in the query statement.
Each substitution variable identifies an input parameter whose mapped value will be substituted at runtime. The type information for the input parameters is fetched from the database using the selected connection for the entered query. Similarly, input fields on the Input tab of the activity are also populated based on the query.
Note: Be sure to include the semicolon (;) at the end of the query. This activity expects an update query to end with a semicolon to indicate the end of the query. The metadata for the tables in the query are not fetched until the statement is completed with a semicolon.

The following example represents a typical update query: 

UPDATE pet SET Name=?Name , Color = ?Color WHERE Species = 'cat';

For the above query all rows in the 'pet' table will have their Name and Color columns set to the values provided on the Input tab where the species is cat. As usual all rows satisfying the where clause will be updated. If the where clause is omitted then ALL rows are updated. The limit clause can also be used to control this behavior.

It is also possible to use a subquery to derive the values to be used in the update. The subquery format follows the general form: 
UPDATE table_name
SET column1 = ?column1, column2 = ?column2...., columnN = ?columnN
WHERE [condition];
In this case the parameters supplied are used in the select query that provides replacement values in the outer update statement. The where clause could also be parameterized.
Manually Configure Metadata If you see an error for a valid SQL statement in the Query field, set the Manually Configure Metadata field to True to fetch table metadata manually using simple SELECT statement. For more information, see Manually Configuring Metadata.
Fields The grid is provided for informational purpose only and documents the fields that will be made available as parameters on the input tab.

Input

This tab displays the input schema of the activity in a tree structure format. The information in the schema varies based on the update query provided. The fields that were selected on the Input Settings tab will be available in the schema. You can either hard code the values or map them to a field from the output of a preceding activity in the flow using the Mapper.

Substitution parameters from the query statement will be provided in the Parameters node.

Output

The Update activity returns the number of rows affected by the query.

Related concepts
Related reference