It would be useful to make DB Merge work when all columns have been selected as WHERE and there are none left to select as SET. This situation arises when the DB table represents a many-to-many relationship that has no attributes. In that case, the node would add to the DB table only the rows of the KNIME table whose combination of WHERE columns does not exist already in the DB table. For the rest, nothing would be done, as there are no columns to update.
Hello @mpenalver,
seems more appropriate to me to have this functionality inside DB Insert node as you are only inserting values not present in db table.
Anyways have created ticket (Internal reference: AP-15380). Someone will update this topic once there are news about it.
Br,
Ivan
As I see it, DB Insert’s behaviour is the right one, throwing an error if the uniqueness constraint associated to the primary keys would be violated by inserting a record that already exists. DB Merge, on the other hand, is meant to handle existing records gracefully; we just need to add the case when no attributes are updated. Alternatively, we could allow to set the attributes included in the WHERE clause (as the SQL statement UPDATE does), and let the DBMS raise an error if the attribute could not be changed e.g. because it is an automatically generated value.
1 Like
Hi @mpenalver,
hmm… You are probably right as seems to me you know a bit more about it than I do 
Br,
Ivan
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.