transformations:deletedbrowsbykeys
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revisionLast revisionBoth sides next revision | ||
transformations:deletedbrowsbykeys [2017/05/02 23:00] – created dmitry | transformations:deletedbrowsbykeys [2021/07/19 02:02] – [Examples] craigt | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Delete matching database rows ===== | + | {{ transformations: |
+ | ====== | ||
+ | Category: Workflow / External\\ | ||
- | Deletes | + | \\ |
+ | =====Description===== | ||
+ | This action deletes | ||
- | In alternative mode, deletes rows where a combination of key fields | + | In the alternative mode, this action |
- | Under the hood the transformation creates a temporary table in the target database, uploads key field combinations into it and performs DELETE..WHERE ..EXISTS statement. After the statement is executed the temporary table is deleted. | + | \\ |
+ | =====Use cases===== | ||
+ | This action can be used for deleting records which IDs are obtained from another data source (e.g. text file or spreadsheet). | ||
- | The operation is //atomic//, meaning that it either | + | \\ |
+ | =====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). | ||
+ | |||
+ | Note that this operation can' | ||
+ | |||
+ | 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 parameters: | ||
+ | > 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 parameters: | ||
+ | > 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: | ||
+ | * [[: | ||
- | **Use cases** | ||
- | This transformation can be used for deleting records which IDs are obtained from another data source (e.g. text file or spreadsheet). |
transformations/deletedbrowsbykeys.txt · Last modified: 2021/07/19 02:02 by craigt