I need to update a field from a table. In order to get to that specific table, I need to go through multiple other tables. To store the relationship between the tables, I have created a metadata table and defined there a hierarchical structure as following:
| Table | level | Tree | join condition |
|---|---|---|---|
| tab1 | 1 | 2 | |
| tab2 | 2 | 3 | tab2.fk = tab1.pk |
| tab3 | 3 | 4 | tab3.fk = tab2.pk |
| tab4 | 2 | 5 | tab4.fk - tab1.pk |
| tab5 | 5 | 6 | tab5.fk = tab4.pk |
| tab6 | 6 | 7 | tab6.fk = tab5.pk |
Using a CTE, I construct a temporary table which stores all the final tables in which I need to update the fields plus its path and it looks like this:
| TableToUpdate | Path |
|---|---|
| Tab1 | tab1 |
| Tab3 | tab1\tab2\tab3 |
| Tab6 | tab1\tab4\tab5\tab6 |
Now, I need to create the update statement, in such way, that the statement knows the joins and the path for each final table to be updated. Do you have any suggestion how should I implement it in wherescape - snowflake? To create dynamic queries in wherescape, should I use java language or is it possible to do it using sql?
Many thanks!