Table of Contents
UPDATE DATABASE TABLE
Category: Workflow / External
Description
This action updates a database table with data from EasyMorph.
Action settings
| Setting | Description |
|---|---|
| Connector* | Select or create a connector to the database. |
* Setting can be specified using a parameter.
Update settings
| Setting | Description |
|---|---|
| Table name* | 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
Matching
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.
Matching data types
Note that matching number fields with non-integer values is not permitted.
The reason is that in EasyMorph the Number data type is 128-bit decimal, not integer. Matching non-integer decimals in EasyMorph with floats or decimals with another bit length in the target database can potentially be incorrect due to rounding errors and therefore is not permitted. Typically, matching should be done by primary or foreign key fields (that are text, integer, or GUID) and never by non-integer values such as price or amount.
Therefore, only these data types can be matched:
- Text (including alpha-numeric text strings)
- Integer numbers
- Dates without the time part
- GUIDs
Do not use these data types for matching:
- Floats or non-integer numbers (such as price or amount)
- Dates with the time part (such as timestamps)
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 |