DELETE MATCHING DATABASE ROWS

Category: Workflow / External

Description

This action deletes rows in a database table where a combination of key fields matches a combination of key fields in the EasyMorph table.

In the alternative mode, this action deletes rows where a combination of key fields doesn't match any combination of key fields in the EasyMorph table.

Use cases

This action can be used for deleting records which IDs are obtained from another data source (e.g. text file or spreadsheet).

Action settings

Selecting the "Edit query" button opens the Query Editor for selecting the Connector and entering SQL queries.

Setting	Description
Connector^*	Select or create a connector to the database.
Table^*	Select the table from the connected database containing the fields that will be used for comparison.
Mode	Choose whether records with matching values will be deleted or retained (and non-matching rows are deleted). Options: Delete rows where keys match or Delete rows where keys DON'T match.
Field mapping	Select the column in the current dataset ("This table"), and the associated field in the connected database ("Database table"), containing the keys to be compared. More than one set of fields may be selected by using the "Add more matching columns" option.

^* Setting can be specified using a parameter.

Remarks

Under the hood, rows are deleted using SQL DELETE statement (with the WHERE statement from the query). The operation is atomic meaning that either all satisfying rows are successfully deleted, or nothing is deleted (in case of some failure).

Note that this operation can't be undone – database rows are deleted forever.

Using a query in the action properties provides the convenience of previewing what rows will be deleted before actually deleting them.

The key-matching process in this action is not case sensitive.

Examples

Example 1: Remove all the database records matching the country names in the source table.

Source table:

Countries to remove
France
United Kingdom
Uruguay

Database table: Capital Cities by Country

Country	Capital City
Canada	Ottawa
France	Paris
Germany	Berlin
Italy	Rome
Mexico	Mexico City
Spain	Madrid
Switzerland	Bern
United Kingdom	London
United States	Washington D.C.
Uruguay	Montevideo

Action parameters:

Connector is the connector to the database.
Table is "Capital Cities by Country"
Mode is "Delete rows where keys match"
Match the fields "Countries to remove" (This table) to "Country" (Database table)

Updated database table:

Country	Capital City
Canada	Ottawa
Germany	Berlin
Italy	Rome
Mexico	Mexico City
Spain	Madrid
Switzerland	Bern
United States	Washington D.C.

Example 2: Remove database records where the Country and Major City do not match the source table.

Source table:

Country	City
Canada	Toronto
Canada	Edmonton
United States	Chicago
United States	Phoenix

Database table: Major Cities by Country

Country	Major City
Canada	Toronto
Canada	Montreal
Canada	Vancouver
Canada	Edmonton
United States	New York City
United States	Chicago
United States	Houston
United States	Phoenix

Action parameters:

Connector is a connector to the database.
Table is "Major Cities by Country"
Mode is "Delete rows where keys DON'T match"
Match fields "Country" (This table) to "Country" (Database table), and "City" (This table) to "Major City" (Database table)