I get data from a CRM system and match it with the records in user accounts table in a postgres database. Wherever, I don’t find a match, I am inserting a record in the user accounts table which creates their login information. Thereafter, the application that is using the database sends an activation email to the user to setup their login password and obtain access to the application.
For the matching user account records, I want to update their records with any changes in their contact information (name, address, phone number, etc.) in the user accounts table in the database.
I am unable to find an action to update database table records using EasyMorph. Can someone help me find the right action if there exists one?
I have read in forum posts that updating is currently not supported in EasyMorph. The suggested solution is to save records in temporary storage, delete the matching records and insert the records in the database table from the CRM. This would not work for my use case since these are user account records that have permissions, login information and other dependent data associated with each user.
How can I solve the issue of updating database data for matching records?
If relatively few records need to be updated, then you can try composing a custom SQL UPDATE statement using expressions, and then executing it with the help of the “Database command” action, and its command “Custom SQL”.
However, this may work too slowly if there are many records to update - e.g. tens of thousands or more.
It is a small number of records at this time. I tried the custom SQL but I was not able to reference any EasyMorph table fields in the SQL UPDATE statement. Can you please help me with this?
Here is a sample project that generates and executes a custom SQL UPDATE statement.
It requires specifying the target table’s name, and the primary key field name. You will also need to specify a database connector in the module “Update DB”.
The Main module uses the “Iterate” action to call the “Update DB” module, but actually it’s only to assign parameters with column values (“Call” can’t do it). The table will always have 1 row, therefore “Update DB” will be called only once.
The project takes 1 row of data, generates and executes an statement such as:
UPDATE Table_target
SET A = 123, B = "AAA"
WHERE ID = 1
There is a basic type conversion for text, number and booleans. It may be necessary to add more rules, e.g. to handle dates.
That’s correct. The project is for updating a single row. It can be modified to do generate multiple UPDATE statements, but it won’t much improve performance, especially if you only have a few rows to update.
I need your help. I have a data set with 4 records and each record has 5 fields including the primary key. I want to develop a dynamic solution wherein I don’t want to create parameters for each field I want to pass to the module when I iterate over the rows.
In your example, assume the sample data is made up of 4 records instead of one. How can I dynamically separate each record and call the update database module per record without having to pass each field in a separate parameter?
if you look closer at the example you will see that it doesn’t require creating a parameter for each field. The called (iterated) module has 3 parameters for any number of fields updated, so it can work with any dataset consisting of any number of columns. You only need to use parameters to provide the database table name, and the key field name.
Parameters of the module “Update DB”:
Table - the name of the database table to update Where - the matching condition for the key field, e.g. CustomerID=123 Assignment - all the fields to be updated and the new values, e.g. Email="jdoe@corp.com",Name="Jane Doe", etc. All columns except the key field are sequenced in this parameter.
I understand this part is dynamic. However, the creation of Assignment and Where clause is one record at a time. When I have multiple records to begin with, the Assignment and Where clause is created for multiple records and that is not correct. We only want to operate on one record at a time so the update is for each record.
