DYNAMIC update statement preparation

[Nan1]

Hi all,

I have a scenario to create dynamic update statement as output.

table 1:

identifier tablename updfield keycolname

US CUSTOMER code, age acctno,date

ET INS name date

table2:

identifier updfiledvalue keycolvalue

US 99,'24F' 2525,'2017-01-14'

ET 'som' '2014-12-12'

output file should look like this:

---Need update statement script as output records

update <>;

update <>;

<like below based on above value>

<update statement of first record>

UPDATE CUSTOMER

SET CODE=99, AGE='24F'

WHERE

ACCTNO=2525 AND DATE='

'2017-01-14';

<update statement of second record>

scenarios:

1. the number of columns to get updated is based on table entry(may be one or more than 1 column based on entry)
2. the number of keycolumns in where clause is also based on table entry
3. identifier is common column for both table
4. i may have many update for single identifier
5. if same entries are to be updated again, we must omit that

can anyone please help me on this.

[tnewbie]

This is an interesting situation.

Because your to-be updated columns keep changing, I would create a view on the fly using tDBRow and try to update that view instead of a table. When I had performance issues in one of my earlier jobs where I was using SCD, I used this approach and it works like a charm. The catch here is, for your view definition to change for every incoming row, you have to use an iterator, if not your view definition will not change. Additionally you have to take in to consider the performance impact, because if you want a DDL to be triggered for every incoming row then it does add a lot of overhead to the process.

[Nan1]

i need the method/job flow of achieving this. can anyone please help

[JohnRMK]

Hello,

I will try to detail as much as possible.

If I understood correctly, you want to create dynamic DMLs according to the data, the columns, the input tables!

The first step is to centralize the metadata, ie, retrieve and control the names of tables, columns and primary keys. You can use https://dataedo.com/kb/query/mysql/list-tables-with-their-primary-keys for example or another database system.

The second step is to get the columns from the CSV files. so either you provide them as a parameter (if it doesn't change often) or you can read the first line of the file and normalize eg

The next step is to detect changes in your data. so, there the tMap is your friend and you get the rejection of join or others; it's up to you to define how you want to detect changes. you can use a comparison on a date, or temporary table, with an except, ...

The last is to build your DML, here is an example code using a column list and data. you can draw inspiration from it

It's just an algo, actually you will use a tFlowToIterate to store each value of your columns in memory then a tJava to construct your query, finally you concatenate everything or you execute it with a tDBRow.

String UpdateStatement =" Update " + context.Target_Table + " SET ";

String WhereClause =" Where ";

for (int i = 0; i < columns.getColumnCount(); i++) {  

 UpdateStatement= UpdateStatement + " " + column.getName() +"='" +columns.getColumnValue(i) +"', ";

 WhereClause += context.Target_Table+"."+column.getName() +"='"+ columns.getColumnValue(i)+"'";

} 

context.Update_Query = UpdateStatement + WhereClause;