How to use a table value in a custom SQL - Tableau Prep

Question

In Tableau Prep I have an output table that I want to import into an Oracle database.

In that output table, there's a column (file_date) with a date value (ex.: '2021-01-01'). The date value is the same for all rows.

Output table:

enter image description here

I need to write a custom SQL query (in Tableau Prep) that checks if my Oracle table already has any rows where the date = '2021-01-01'. If so, all rows need to be deleted before I import the new data.

Table_1:

enter image description here

Something like:

DELETE FROM table_1 WHERE date_column = '2021-01-01';

After checking it will find that the 1st row has the date = '2021-01-01' and deletes that row.

Table_1 after:

enter image description here

As the date changes every time new files come up, manually entering the date in the query is not a possibility. Is there any way to use a value from my table in a custom SQL query?

I'm aware that Tableau Desktop allows for the creation of parameters, but that's not available in Tableau Prep.

Welcome to the SO Community, The community will help you with issues. But there are certain expectations. Please take a few minutes to take the Tour and to review How to Ask. Post table definition (ddl) and sample data, as text - no images. For this there are defiantly ways other than changing the query each time. But how do you know what the date is? Is it the oldest date on your table? The newest? Is it on a parameter table? You get a memo from Bill in accounting? Others? Without that it is impracticable to suggest how to automate this. — Belayer
@Belayer in the output (in Tableau Prep) the date is the same for all rows. — dataAnalyst

Belayer Belayer · Accepted Answer · 2021-08-31T18:12:06

If the output is all the same date, then that must also be true of the input data. There is no reason to check for existing data values. Since finding no matching records on delete does not throw an error, checking is not necessary - just delete.

delete 
  from table_1 
 where date_column =  
       (select date_column 
          from import_table_1 
          fetch first 1 row only  
       );

How to use a table value in a custom SQL - Tableau Prep

1 Answers