transformations:deletedbrowsbykeys
no way to compare when less than two revisions
Differences
This shows you the differences between two versions of the page.
Previous revisionNext revision | |||
— | transformations:deletedbrowsbykeys [2021/07/03 21:17] – craigt | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | {{ transformations: | ||
+ | ====== 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 // | ||
+ | |||
+ | \\ | ||
+ | =====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 | ||
+ | |Connector< | ||
+ | |Table< | ||
+ | |Mode|Choose whether records with matching values will be deleted or retained (and non-matching rows are deleted).\\ | ||
+ | |Field mapping|Select the column in the current dataset ("This table" | ||
+ | < | ||
+ | |||
+ | \\ | ||
+ | =====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: | ||
+ | ^Country | ||
+ | |Canada | ||
+ | |France | ||
+ | |Germany | ||
+ | |Italy | ||
+ | |Mexico | ||
+ | |Spain | ||
+ | |Switzerland | ||
+ | |United Kingdom | ||
+ | |United States | ||
+ | |Uruguay | ||
+ | \\ | ||
+ | **Action settings:** | ||
+ | > Connector is the connector to the database. | ||
+ | > Table is " | ||
+ | > Mode is " | ||
+ | > Match the fields " | ||
+ | |||
+ | \\ | ||
+ | **Updated database table:** | ||
+ | ^Country | ||
+ | |Canada | ||
+ | |Germany | ||
+ | |Italy | ||
+ | |Mexico | ||
+ | |Spain | ||
+ | |Switzerland | ||
+ | |United States | ||
+ | |||
+ | |||
+ | \\ \\ | ||
+ | **EXAMPLE 2:** Remove database records where the Country and Major City //do not match// the source table. | ||
+ | |||
+ | **Source table:** | ||
+ | ^Country | ||
+ | |Canada | ||
+ | |Canada | ||
+ | |United States | ||
+ | |United States | ||
+ | |||
+ | **Database table: | ||
+ | ^Country | ||
+ | |Canada | ||
+ | |Canada | ||
+ | |Canada | ||
+ | |Canada | ||
+ | |United States | ||
+ | |United States | ||
+ | |United States | ||
+ | |United States | ||
+ | \\ | ||
+ | **Action settings:** | ||
+ | > Connector is a connector to the database. | ||
+ | > Table is "Major Cities by Country" | ||
+ | > Mode is " | ||
+ | > Match fields " | ||
+ | |||
+ | \\ | ||
+ | **Updated database table:** | ||
+ | ^Country | ||
+ | |Canada | ||
+ | |Canada | ||
+ | |United States | ||
+ | |United States | ||
+ | |||
+ | |||
+ | \\ | ||
+ | =====See also===== | ||
+ | * [[transformations: | ||
+ | * [[transformations: | ||
+ | * [[transformations: | ||
+ | * [[: | ||
transformations/deletedbrowsbykeys.txt · Last modified: 2021/07/19 02:02 by craigt