In v4.4 we’ve added the “Update database table” action that simplifies updating data in database tables with values from EasyMorph. Instead of 5 actions required previously, you can now update DB tables using just 1 action.
Here is an illustration how it works:
Data before:
The goal is to replace “M” with “Married” and “S” with “Single” in the [MaritalStatus] field. Here is an according “Update database table” action:
The result:
Under the hood, the action generates an SQL UPDATE statement in the following format:
UPDATE db_table_name
SET db_field1 = easymorph_value1 , db_field2 = easymorph_value2 , ...
WHERE db_key_field1 = easymorph_key_value1 AND db_key_field2 = easymorph_key_value2 AND ...;
Note that each combination of values in matched key columns in EasyMorph must be unique, and correspond to only 1 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]:
This is wrong and will make the action fail:
It’s also possible to not specify matching key fields at all. In this case, the EasyMorph dataset must have only 1 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 1 row, the action will fail.