SQL Update Where clause

Hi. I’m looking for a way to read in a Knime data table and for each row, perform a sql update (Oracle) with a where clause.

Pseudo code example:

update record set insert_date = (TO_TIMESTAMP_TZ(CURRENT_TIMESTAMP, 'DD-MON-RR HH.MI.SSXFF PM')) WHERE record_id = <value_from_incoming_table_column>

I’ve looked at the DB SQL Executor and the DB Update but can’t see a way to do this

Hi @weatherh,

you could do it with the DB Update Node https://nodepit.com/node/org.knime.database.node.io.update.DBUpdateNodeFactory

However you need to get the current timestamp through the


(select execution time)

Then put it into your table

If you now name your timestamp column “insert date” you can use it in the update node to set your db column.

Sadly this workaround is needed as far as I know, as the db update node only works if the columns from the knime table Match to the database table

2 Likes

Hi there @weatherh,

welcome to KNIME Commuinity!

What was the issue with DB Update node? As @AnotherFraudUser you need both columns (record_id and insert_date) in your table and should work as expected…

Br,
Ivan

1 Like

Thank you @ipazin and @AnotherFraudUser. Much appreciated. Maybe I’m misreading what DB Update node does but it seems to require a one to one mapping of the Knime data table column name to an Oracle DB column name. Is that correct? The other gap is that I do actually want to use the time from the Oracle host rather than the Knime client and insert the time that the record was actually updated. I’ve done this before with other similar tools and I’m trying to understand what the similar process looks like in Knime.

Hi @weatherh,

Yes you need to have an exact mapping.
If you want to have the ORACLE default value, then it would be more complicated to do that :thinking:
How many values are you updating? a few hundred, thousand,million?
If it is just hundred then you could create the in statement though an groupby node.

If is is thousand/million rows I think even a loop would not be great…
Can you change the default value of your update table to DEFAULT CURRENT_TIMESTAMP? Then you would set the value direcly with the insert of new rows

But I am not sure if there is a pretty purely in KNIME solution :confused:

1 Like

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