Deleting data from a table

Use the Graphical Data Mapping editor to delete data from 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 > Delete from Table. Alternatively, click the Delete a row from a database table icon.
   
   The New Database Table Delete From 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 the rows that you want to delete 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:

   • Ensure that the case of your table and column names match that of your database.
   • Avoid the use of double-quotes around table and column names.
   • Use only the supported SQL keywords that are presented in the Operators pane.
   • Ensure that each parameter placeholder is inserted as a question mark followed by an optional unique number and a space character, and also ensure that each parameter placeholder is defined with an XPath expression in the parameter table below the SQL where clause.
7. Click OK.
   A Delete 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 that provides a nested mapping. It is entered only if the associated Delete was successful. If you do not need to use the Return transform, you can delete it from your graphical data map.
8. Optional: To replace a Return transform that you deleted from your graphical data map, right-click your Insert transform and select Database > Utilize return.
9. Optional: Connect the Return transform to implement a nested mapping that is called if the Delete operation was completed successfully.
10. 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.