User Tools

Site Tools


transformations:updatedb

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 matchSelect the database field and the EasyMorph dataset column containing matching values.
Column mappingSelect 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 convertedChoose 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 handlingChoose 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 updatingCheck 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 2Engineering M M
219 4Document Coordinator S M
241 2Accounts Manager M F
244 3Accounts Receivable S M
246 3Accounts Payable M F
248 3Accountant 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 2Engineering Married M
219 4Document Coordinator Single M
241 2Accounts Manager Married F
244 3Accounts Receivable Single M
246 3Accounts Payable Married F
248 3Accountant Single F


Community examples

See also

transformations/updatedb.txt · Last modified: 2023/10/15 21:05 by craigt

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki