Icon

kn_​example_​db_​h2_​update_​in_​databse

H2 do an UPDATE on the server using a VIEW of the existing and the new data

H2 do an UPDATE on the server using a VIEW of the existing and the new data

H2 do an UPDATE on the server using a VIEW of the existing and the new datadepending on your database the syntax might be different UPDATE "PUBLIC"."my_database" AS t1 SET t1."is_updated" = (SELECT t2."is_updated" FROM "PUBLIC"."my_update_view" AS t2 WHERE t2."new_id" = t1."new_id" AND t2."random_01" = t1."random_01" AND t2."random_02" = t1."random_02" ) , t1."when_updated" = (SELECT t3."when_updated" FROM "PUBLIC"."my_update_view" AS t3 WHERE t3."new_id" = t1."new_id" AND t3."random_01" = t1."random_01" AND t3."random_02" = t1."random_02" ) my_database = > the main databasemy_update => the table with the new valuesmy_update_view => a VIEW preparing the old and new values DROP VIEW IF EXISTS "PUBLIC"."my_update_view";CREATE VIEW "PUBLIC"."my_update_view" AS SELECT t1."new_id" , t1."random_01" , t1."random_02", CASE WHEN t2."is_updated" IS NOT NULL THEN t2."is_updated" ELSE t1."is_updated" END AS "is_updated", CASE WHEN t2."when_updated" IS NOT NULL THEN t2."when_updated" ELSE t1."when_updated" END AS "when_updated" FROM "PUBLIC"."my_database" AS t1 LEFT JOIN "PUBLIC"."my_update" AS t2 ON t2."new_id" = t1."new_id" AND t2."random_01" = t1."random_01" AND t2."random_02" = t1."random_02" ; in a real world scenario this data would already be there current timestart_timeknime://knime.workflow/data/my_database.h2transfer to table my_database=> adapt batch sizeDROP VIEW IF EXISTS "PUBLIC"."my_update_view";DROP TABLE IF EXISTS "PUBLIC"."my_database";DROP TABLE IF EXISTS "PUBLIC"."my_update";create tablemy_databasestart_timerandom_01random_02is_updatedNOwhen_updatedwhen_updatedcreate tablemy_updatetransfer to tablemy_update=> adapt batch sizestart_timecurrent timestart_time15 seconds20% randomcasesis_updatedYESwhen_updatedwhen_updatednew_idNode 1955UPDATE "PUBLIC"."my_database"CREATE VIEW "PUBLIC"."my_update_view"check result of the VIEWsort bynew_idCreate Date&TimeRange H2 Connector DB Writer DB SQL Executor DB Table Creator Table Rowto Variable Data Generator Random NumberAssigner (Apache) Random NumberAssigner (Apache) ConstantValue Column ConstantValue Column String to Date&Time DB Table Creator DB Writer Table Rowto Variable Create Date&TimeRange Wait... Partitioning ConstantValue Column ConstantValue Column String to Date&Time Java Snippet(simple) Column Resorter DB SQL Executor DB Table Selector DB Reader DB Reader DB SQL Executor DB Table Selector DB Reader DB Sorter H2 do an UPDATE on the server using a VIEW of the existing and the new datadepending on your database the syntax might be different UPDATE "PUBLIC"."my_database" AS t1 SET t1."is_updated" = (SELECT t2."is_updated" FROM "PUBLIC"."my_update_view" AS t2 WHERE t2."new_id" = t1."new_id" AND t2."random_01" = t1."random_01" AND t2."random_02" = t1."random_02" ) , t1."when_updated" = (SELECT t3."when_updated" FROM "PUBLIC"."my_update_view" AS t3 WHERE t3."new_id" = t1."new_id" AND t3."random_01" = t1."random_01" AND t3."random_02" = t1."random_02" ) my_database = > the main databasemy_update => the table with the new valuesmy_update_view => a VIEW preparing the old and new values DROP VIEW IF EXISTS "PUBLIC"."my_update_view";CREATE VIEW "PUBLIC"."my_update_view" AS SELECT t1."new_id" , t1."random_01" , t1."random_02", CASE WHEN t2."is_updated" IS NOT NULL THEN t2."is_updated" ELSE t1."is_updated" END AS "is_updated", CASE WHEN t2."when_updated" IS NOT NULL THEN t2."when_updated" ELSE t1."when_updated" END AS "when_updated" FROM "PUBLIC"."my_database" AS t1 LEFT JOIN "PUBLIC"."my_update" AS t2 ON t2."new_id" = t1."new_id" AND t2."random_01" = t1."random_01" AND t2."random_02" = t1."random_02" ; in a real world scenario this data would already be there current timestart_timeknime://knime.workflow/data/my_database.h2transfer to tablemy_database=> adapt batch sizeDROP VIEW IF EXISTS "PUBLIC"."my_update_view";DROP TABLE IF EXISTS "PUBLIC"."my_database";DROP TABLE IF EXISTS "PUBLIC"."my_update";create tablemy_databasestart_timerandom_01random_02is_updatedNOwhen_updatedwhen_updatedcreate tablemy_updatetransfer to tablemy_update=> adapt batch sizestart_timecurrent timestart_time15 seconds20% randomcasesis_updatedYESwhen_updatedwhen_updatednew_idNode 1955UPDATE "PUBLIC"."my_database"CREATE VIEW "PUBLIC"."my_update_view"check result of the VIEWsort bynew_idCreate Date&TimeRange H2 Connector DB Writer DB SQL Executor DB Table Creator Table Rowto Variable Data Generator Random NumberAssigner (Apache) Random NumberAssigner (Apache) ConstantValue Column ConstantValue Column String to Date&Time DB Table Creator DB Writer Table Rowto Variable Create Date&TimeRange Wait... Partitioning ConstantValue Column ConstantValue Column String to Date&Time Java Snippet(simple) Column Resorter DB SQL Executor DB Table Selector DB Reader DB Reader DB SQL Executor DB Table Selector DB Reader DB Sorter

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: m_lauber
Created at: 2021-12-29
On NodePit since: 2022-02-18
Last update: 2024-04-26
Created with KNIME version: v4.5.1
Tags: h2updateappenddatabseSQL

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!