Issues with upsert operation in data comparison

[Cypher]

I am doing a comparison between two data sources, one a google sheet the other a database. I’m attempting to do an upsert at the end and all was working until I added another data point that is nullable. The workflow is supposed to transfer data from an internal sheet that houses some user information, then updates our database with it. However, some data can change like first names and last names, but there are some immutable data points. There is another workflow that fires off after this one that generates a user inside of our CMS and then updates the user’s information with the ID assigned by that CMS. For some reason, when adding that data point and making it nullable, the upsert no longer attempts to search for the immutable data and just inserts a new instance of the data.

Please share your workflow

Share the output returned by the last node

Information on your n8n setup

• n8n version:1.11.1
• Database: MySQL
• n8n EXECUTIONS_PROCESS setting (default: own, main):
• Running n8n via (Docker, npm, n8n cloud, desktop app): Cloud VPS
• Operating system: Alma 8

[n8n]

It looks like your topic is missing some important information. Could you provide the following if applicable.

• n8n version:
• Database (default: SQLite):
• n8n EXECUTIONS_PROCESS setting (default: own, main):
• Running n8n via (Docker, npm, n8n cloud, desktop app):
• Operating system:

[MutedJam]

Hi @Cypher, I am very sorry you are having trouble. Unfortunately I was not able to reproduce the problem based on your description.

Could perhaps confirm your table structure and share an example of a JSON dataset returned by your Find the non-matches that is not updating as expected despite the respective id value existing in your db table?

Thank you very much!

[Cypher]

Hi @MutedJam, I can provide the info requested, now mind you a lot of this data is just generated by a script so it’s non-legitimate.

The JSON return from the “Find non-matches” node is as follows:

[
{
"fullname": 
"Full Name 686",
"firstname": 
"First Name 7",
"lastname": 
"Last Name 513",
"email": 
"[email protected]",
"user_code": 
"PUTT"
}
]

The user_code is assigned upon creation of an account, it’s one of the only immutable data points within this data. The upsert attempts to match the user in the database by the user_code, if it can’t find it then it just creates a new one. However, where it seems to be running into the issues is with the CMS user ID that gets updated in the database after this operation happens, so there are two workflows right now and they go as such:

workflow 1
Check the spreadsheet, compare, find a match(update) or not(create)

workflow 2
Check the database, run it against the REST API to check if the user exists, if it doesn’t create it and update that user in the database with the ID from the CMS.

I hope I’m not being too confusing, right now I’m just working with workflow 1, it was working perfectly fine until the introduction of the CMS ID being in the table and allowing it to be nullable.

For example, this is what the JSON from a query to the database looks like:

{
"id": 
1,
"fullname": 
"Full Name 957",
"firstname": 
"First Name 202",
"lastname": 
"Last Name 992",
"email": 
"[email protected]",
"user_code": 
"VCZR",
"wp_user_id": 
82
}

That wp_user_id is for storing a user ID that gets returned when the wordpress user is created via the REST API, but if it’s null on workflow 1’s upsert, it just creates a new instance of the data instead of updating the one it matches on.

[MutedJam]

Thanks for confirming @Cypher! It looks like the data coming from your “Find non-matches” node does not include an id value. Since this is the field using which your MySQL node uses to check if a dataset already exists, it would result in a new row.

Perhaps you want to write a suitable Upsert query taking the possible data structures into account and then executing it through the Execute SQL operation of the MySSQL node?