Differences

This shows you the differences between two versions of the page.

--- transformations:deletedbrowsbykeys [2017/05/02 23:00] – created dmitry
+++ transformations:deletedbrowsbykeys [2021/07/19 02:02] (current) – [Examples] craigt
@@ Line 1: / Line 1: @@
-===== Delete matching database rows =====
+{{ transformations:DeleteDbRowsByKeysAction.png}}
+====== DELETE MATCHING DATABASE ROWS ======
+Category: Workflow / External\\
-Deletes rows in database table where a combination of key fields matches a combination of key fields in EasyMorph table.
+\\
+=====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 alternative mode, deletes rows where a combination of key fields DOESN'T MATCH any combination of key fields in 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.
-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 successfully deletes all matching (or non-matching) rows, or doesn't delete anything if a failure occurs.
+\\
+=====Action settings=====
+Selecting the "Edit query" button opens the Query Editor for selecting the Connector and entering SQL queries.
+^ Setting  ^ Description  ^
+|Connector<sup>*</sup>|Select or create a connector to the database.|
+|Table<sup>*</sup>|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.|
+<sup>*</sup> Setting can be specified using a [[:parameters|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:sqlcommand|Database command]]
+  * [[transformations:deletedbrows|Delete database rows]]
+  * [[transformations:updatedb|Update database table]]
+  * [[:connectors|Information on connectors]]
-**Use cases**
-This transformation can be used for deleting records which IDs are obtained from another data source (e.g. text file or spreadsheet).