{{ transformations:DeleteDbRowsByKeysAction.png}}
====== 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<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]]