User Tools

Site Tools


transformations:deletedbrowsbykeys

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.
ModeChoose 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 mappingSelect 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)


Updated database table:

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


See also

transformations/deletedbrowsbykeys.txt · Last modified: 2021/07/19 02:02 by craigt

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki