Differences

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

--- transformations:updatedb [2020/07/06 06:57] – created dmitry
+++ transformations:updatedb [2021/07/27 01:23] – craigt
@@ Line 1: / Line 1: @@
-===== Update database table =====
+{{ transformations:UpdateDbAction.png}}
+====== UPDATE DATABASE TABLE ======
+Category: Workflow / External\\
-This action updates values in a database table with values from EasyMorph.
+\\
+=====Description=====
+This action updates a database table with data from EasyMorph.
+\\
+=====Action settings=====
+^ Setting  ^ Description  ^
+|Connector<sup>*</sup>|Select or create a connector to the database.|
+<sup>*</sup> Setting can be specified using a [[:parameters|parameter]].\\
+\\
+====Update settings====
+^Setting ^Description ^
+|Table name<sup>*</sup>|Select the table in the database with the values to be updated.|
+|Update rows where these columns match|Select the database field and the EasyMorph dataset column containing matching values.|
+|Column mapping|Select the EasyMorph dataset column containing the updated values, and the database field to be updated. |
+\\
+====Options settings====
+^Setting ^Description ^
+|When value doesn't match column type and can't be converted|Choose how EasyMorph will handle cases where a value to be written to the database table is not the appropriate data type, and it cannot be converted to the appropriate type.  Options:  //Write as NULL// (values are inserted as NULL values) or //Fail batch// (the batch fails and no updates are made).|
+|Empty value handling|Choose how EasyMorph will handle empty values in the (source) dataset table.  Options:  //Empty values are written as NULLs// or //Update with non-empty values only, ignore empty ones//.|
+\\
+====Custom SQL settings====
+^Setting ^Description ^
+|Execute custom SQL before updating|Check this option to run a custom SQL statement prior to the final update of the database table.  Enter the custom SQL in the field that appears.|
+\\
+=====Remarks=====
+Note that each combination of values in matched key columns in EasyMorph must be unique, and correspond to only one row, in order to avoid ambiguity.
+For instance, this is correct because every value in [Marital Status] (key field) corresponds to only one value in [NewMaritalStatus]:
+^MaritalStatus ^NewMaritalStatus ^
+|M  |Married  |
+|S  |Single  |
+The following is incorrect and will make the action fail:
+^MaritalStatus ^NewMaritalStatus ^
+|M  |Married  |
+|S  |Single  |
+|S  |Also single  |
+\\ \\
+It’s also possible to not specify matching key fields at all. In this case, the EasyMorph dataset must have only one row and the underlying SQL UPDATE statement will look as follows:
+  UPDATE  db_table_name
+  SET  db_field1  =  easymorph_value1 ,  db_field2  =  easymorph_value2 , ...
+  ;
+If no matching fields are specified and the EasyMorph dataset has more than one row, the action will fail.
+\\
+=====Examples=====
+**Example:**  Update the marital status abbreviations to full-text status values.
+**Database table:**  Employee demographics
+^BusinessEntity  ^Organization  ^Job Title  ^Marital Status  ^Gender  ^
+|  3|  2|Engineering  |M  |M  |
+|  219|  4|Document Coordinator  |S  |M  |
+|  241|  2|Accounts Manager  |M  |F  |
+|  244|  3|Accounts Receivable  |S  |M  |
+|  246|  3|Accounts Payable  |M  |F  |
+|  248|  3|Accountant  |S  |F  |
+**EasyMorph dataset:**
+^MaritalStatus ^NewMaritalStatus ^
+|M  |Married  |
+|S  |Single  |
+\\
+**Parameter settings:**
+> Connector is (connector to the target database)
+> Table name is "Employee Demographics"
+> Database field is "MaritalStatus"
+> Column or value is "MaritalStatus"  (column within EasyMorph dataset)
+> This table is "NewMaritalStatus" (column with the replacement values)
+> Database is "MartialStatus" (database field to update)
+\\
+**Database table after update:**
+^BusinessEntity  ^Organization  ^Job Title  ^Marital Status  ^Gender  ^
+|  3|  2|Engineering  |Married  |M  |
+|  219|  4|Document Coordinator  |Single  |M  |
+|  241|  2|Accounts Manager  |Married  |F  |
+|  244|  3|Accounts Receivable  |Single  |M  |
+|  246|  3|Accounts Payable  |Married  |F  |
+|  248|  3|Accountant  |Single  |F  |
+\\
+=====See also=====
+  * [[transformations:sqlcommand|Database command]]
+  * [[transformations:deletedbrows|Delete database rows]]
+  * [[transformations:deletedbrowsbykeys|Delete matching database rows]]
+  * [[https://community.easymorph.com/t/announcement-the-update-database-table-action/1964|Announcement: "Update database table" action]]
-For a detailed explanation see [[https://community.easymorph.com/t/annoucement-the-update-database-table-action/1964|Announcement: the "Update database table" action]].