Updating data in a table

Use the Graphical Data Mapping editor to update data in a database table.

1. With a graphical data map (.map) file open in the Graphical Data Mapping editor, right-click the canvas, and select Database > Update Table. Alternatively, click the Update a row in a database table icon.
   
   The New Database Table Update wizard is displayed.
2. In the Database field, select the database that you want to modify. To add a database definition file, or to discover a new database by connecting to a database server, click Add database.... For more information, see Creating a database definition (.dbm file) by using the New Database Definition File wizard.
   To use a different database name at run time, you can override this value by setting the databaseName property of the JDBCProvider configurable service that connects to your database; see Setting up a JDBC provider for type 4 connections.
3. In the Schema field, select the database schema that you want to use to build the transform.
   To use a different database schema at run time, you can override this value by setting the databaseSchemaNames property of the JDBCProvider configurable service that connects to your database; see Setting up a JDBC provider for type 4 connections.
4. In the Table field, select the database table that you want to modify.
5. Optional: Select Treat warning as error.
   If this option is selected, the first SQL operation that results in a warning from the selected database raises an exception.

   Important: Database warnings are vendor-specific. For more information about database warnings, see the documentation for your database product.
6. In the SQL where clause field, use supported SQL to specify the criteria for selecting rows from your database table.

   Build a supported SQL statement by dragging items from the Table columns and Operators panes to the SQL where clause field.

   To include values in your SQL statement, drag items from the Available inputs for column values pane to the SQL where clause to add them as parameters, or type literal values such as 'abc' or 123 directly in the SQL where clause.

   Parameters from the SQL where clause are listed in the XPath expression table. You can edit the XPath expressions to refine the input, for example to add a specific array index for a dragged repeating field. A default SQL where clause is created for you, which selects all rows in your selected database table.

   Note: If you edit the text of the SQL where clause directly, take care to:

   • ensure the case of your table and column names match that of your database.
   • avoid the use of double-quotes around table and column names.
   • only use the supported SQL keywords that are presented in the Operators pane.
7. Optional: Select Insert when a row does not exist if you want to insert a new row in your database table when no existing row meets the criteria of your SQL where clause.
   If this option is selected, the map checks the "number of rows updated" return from the database server for the Update SQL operation. If the "number of rows updated" is zero, the map issues an insert SQL operation. For the insert operation to succeed, your Update transform must explicitly provide valid values for all mandatory database columns. If you want a row that is inserted in this way to use different values to those that are provided by your Update transform, consider adding a conditional Insert transform inside the Return transform.
8. Click OK.
   An Update transform and a Return transform are created as a transform group, and are displayed in your graphical data map. The Return transform is an optional transform type that provides a nested mapping that is entered only if the associated Update was successful. If you do not need to use the Return you can delete it from your graphical data map.
9. Optional: To replace a Return transform that you deleted from your graphical data map, right-click your Update transform and select Database > Utilize return.
10. In the Graphical Data Mapping editor, connect input objects to the Update transform to define the content of your updated row.
    If you connect a repeating element, the Update transform is nested inside a For Each transform, and this nested transform is opened so that you can continue to edit your Update transform.
11. Click the Update transform to create connections to the columns in your updated row, and to further define the transform.
12. Optional: If you need to provide handling for the connected source element being Missing, Empty or Nil, you can set a Database Policy. See Behavior when modifying database column values from optional source elements.
13. Optional: Connect the Return transform to implement a nested mapping that is called if the Update operation was completed successfully.
    The nested Return transform provides a built-in input, "NumberOfRowsUpdated", and additional inputs can be connected.
14. Optional: Click the Return transform to further define the transform.
    A nested map is created, in which you can select the specific transforms that are required for the input and output elements.