💡 Announcement: the "Update database table" action

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.

Dear all,

I tried the method but I receive an error: "update of row #0 failed with the following error: Overflow of an arithmetic operation.

What does it mean?



Hello Andrea and welcome to the Community!

Can you please answer the following questions:

  • What database you are working with?
  • What database connector you are using (native or ODBC?)
  • If you are using an ODBC connector, what are the name and the version of the ODBC driver?
  • What values does the first row of the input dataset has?
  • What datatypes corresponding database columns have?