How can I accomplish on KNIME "INSERT ... ON DUPLICATE KEY UPDATE Statement"?

I want to insert or update some rows based on the unique key. I searched but I did not find any clues on the internet.

This is quite vague. It would be more helpful if you provided a specific example with some data and your desired output.

Hi @bilgee , the INSERT statement will be executed on the server side (database). Knime does not manage/control this.

So, you just need to write your statement as you would in any sql client, and execute it via the DB SQL Executor node:

2 Likes

@bilgee, You might want to also take a look at the DB Merge node, as, if I’m not mistaken, I think the functionality you are describing is possibly the MySql equivalent of the Merge statement found on some other relational databases.

From…
https://docs.knime.com/2020-12/db_extension_guide

"DB Merge
The DB Merge node is a combination of the DB Update and DB Insert node. If the database
supports the functionality it executes a MERGE statement that inserts all new rows or updates
all existing rows in the selected database table. If the database does not support the merge
function the node first tries to update all rows in the database table and then inserts all rows
where no match was found during the update. The names of the selected KNIME table
columns need to match the names of the database table where the rows should be updated. "

2 Likes

Thank you for your reply. I will try it.

Thank you for your reply. I tried DB Merge Node using tiny data but it was very slow. It took almost an hour. I did not know why it was slow.

Thank you for your reply. I uploaded some example images. I hope you will understand

I have 3 rows into the table:
dup1

add 1 more row into the table using the below query:
dup2

The above statement will add row successfully because it does not have any duplicate values.
Finally, I am going to add a row with a duplicate value in the Stud_ID column:
We can see that the row id=4 already exists. So the query only updates the City New York with California.
dup3

Can I do the same operation using Knime?

@bilgee I can demo the use with DB Merge in MySQL, with a workflow, if that helps

KNIME_db_ins_upd.knwf (36.8 KB)

In reality, of course, your data would be coming in from elsewhere, and we’d pass around credentials rather than coding 3 separate mysql connections but I’ve just put it into three individual workflows (1,2,3) so you can manually execute in sequence and look at the output from the DB Query Reader in each case. So you execute just workflow 1 followed by workflow 3, or you could execute 1 then 2 then 3. In both cases the result at 3 should be the same.

I don’t know why your merge would have taken a long time. Could there have been anything locking the table maybe in a separate session?

To test this workflow, obviously you’ll need to change the connection parameters to match your local db, but I think I’ve created the same student table albeit that here it is in my “gnl” schema that you’ll need to change to suit your setup.

2 Likes

Thank you @takbb for your detailed workflow :slight_smile:
I will try your solution.

1 Like

@bilgee I would like to point you to a KNIME-only solution to your task since that might be what you initially asked

Also I created an example using H2 database and Stud_id as PRIMARY_KEY and load everything into a new database

3 Likes

Thank you @mlauber71. I will try your workflow

Can you double check that you have an index on the columns that you use as unique keys (WHERE columns). If you don’t have an index on the columns the database needs to perform a full table scan for every row to check if a match exists.

2 Likes