You can translate the question and the replies:

Hi, I want to update a table on a join condition with another table or can we set a value coming from a select query in the update statement?

NA
user
26-02-2021 07:59:58 -0500

1 Answer

Hi, For this scenario, I would use a Scheduler job to run an **[UPDATE](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/inserts_updates_and_deletes_over_views/update_statement/update_statement#update-statement)** statement using the values retrieved from the SELECT clause (Join Query). You could follow the below mentioned steps: * In the Virtual DataPort Administration Tool, **[create a data source](https://community.denodo.com/docs/html/browse/latest/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#jdbc-sources)** to establish a connection to the source and then **[create base views](https://community.denodo.com/docs/html/browse/latest/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#creating-base-views-from-a-jdbc-data-source)** from it. * Then, in the Scheduler Administration Tool, **[configure a VDP data source](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/creating_and_scheduling_jobs/data_sources/vdp_data_sources#vdp-data-sources)** to connect to the Virtual DataPort. * You could then [**create a VDP job**](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/general_structure_of_a_job#general-structure-of-a-job) and navigate to the **[Extraction](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/vdp_extraction_section#vdp-extraction-section)** section to specify the update and select queries. **For instance: ** In the **[Parameterized query](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/vdp_extraction_section#syntax-of-parameterized-queries)** specify the UPDATE statement like **UPDATE <baseview1> SET column1 = @param_field1, column2 = @param_field2...., columnN = @param_fieldN WHERE [condition]** In the **[Non-Parameterized Query](https://community.denodo.com/docs/html/browse/8.0/en/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/vdp_extraction_section#configuring-the-values-to-be-used-in-a-parameterized-query)** specify the SELECT statement like **SELECT field1, field2...., fieldN FROM <baseview1>JOIN <baseview2> ON [condition] WHERE [condition]** For more information, you can take a look at the **[Creating and Scheduling Jobs](https://community.denodo.com/docs/html/browse/latest/en/scheduler/administration/creating_and_scheduling_jobs/creating_and_scheduling_jobs#creating-and-scheduling-jobs)** section of the Scheduler Administration Guide. Hope this helps!
Denodo Team
02-03-2021 01:11:54 -0500
You must sign in to add an answer. If you do not have an account, you can register here