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 ‘,[email protected],012345,012345,2023-02-22T10:46:26.283+10:0’ at line 2
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.
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
EG:
INSERT INTO customers
(name,phone,email,etc)
VALUES (jon,,,etc)
instead of
INSERT INTO
(name,phone,email,etc)
VALUES ("jon",null,null,etc)
I also can’t seem to use $json.map((element)=>{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
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!
