MySQL - On duplicate key update

Describe the issue/error/question

Hi All,
I’m trying to get data from an API endpoint, and store it in MYSQL

This works, but there is a primary key on my table “id” so I’d like to do “on duplicate key update”
but when i try to do this, it errors with invalid SQL

In the preview window of the node if I copy the SQL it shows as an example it works totally fine

What is the error message (if any)?

ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘,,012345,012345,2023-02-22T10:46:26.283+10:0’ at line 2

Please share the workflow

Hi @Tim_C,

Welcome to the community :tada:

Looking at the error the database is not happy with the query, Do you have multiple items going into the node? It may be worth checking the database logs as well just to confirm the query that is being sent.

Hey Jon,

If I copy the query “example” when I view the node and paste it straight into MySQL, it works fine

I THINK the problem is that “null” is being converted to a literal blank string instead of the word null when it runs


INSERT INTO customers 
VALUES (jon,,,etc)

instead of

VALUES ("jon",null,null,etc)

I also can’t seem to use $>{do something}) at all in the expression

The workaround I have is to query all the primary keys from the database, then use 2 merges to split between insert and update, but this is way heavier on the database as I’m going to repeat this for another table which has 50,000+ rows

Hey @Tim_C,

That would do it, normally I would suggest a conditional in the values but as you are taking a more dynamic approach it won’t be that easy so it could be that a set node or code node first to help with the values is the way forward.

With the arrow function they are currently not supported in expressions.

I will see if we have an internal ticket opened for null vs blank already if not I will open one.

Thanks, I’m currently using compare dataset node to check what needs to be deleted, updated or inserted but again, this massively slows down the workflow, in one of them I have 80,000 objects to compare!

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.