Running a MERGE (SQL) in TALEND

[stephbzr]

Hello, 

Here is a SQL query with MERGE that allows me to UPDATE several rows at once in my database (with a join).

I need to translate this idea into TALEND, but I have no idea how I'm going to do it. The data from ID_TRTM and the data after each "THEN", my TALEND job has them (these are statistics about a file such as number of rows, size, etc..). 

Do you have any ideas ? Thank you.

[Jmiles1658991691 · Accepted solution]

Hi @Stéphane Barbezier​ you can run this query in talend with tDBRow component

[stephbzr]

Thank you very much @Juanita miles​ , I learned what this component does and it works very well for what I am looking for.

[ThWabi]

Hello stephbzr,

as I understand the SQL merge statement, the select after "using" (i.e. the table TMP) is your input data (for a tMap component). The table TMPD is the lookup table and also the output/target.

The "on" condition (TMPD.ID_TRT_MUTIX_PARAM = TMP.ID_TRT_MUTIX_PARAM) is the join between the input and lookup table. As your merge statement only has an "on matched" clause, the join model in the tMap is set to "inner join". (We can discard non-matching rows.)

Please find the uploaded screenshot for a detailed outline.

The "Update_Flag" is the expression

TMP.TRT_MUTIX_PARAM_CODE.equals("NB_LIGNES_FIC") || TMP.TRT_MUTIX_PARAM_CODE.equals("TAILLE_FIC") || TMP.TRT_MUTIX_PARAM_CODE.equals("ID_HIST")

and "Update_Value" is

TMP.TRT_MUTIX_PARAM_CODE.equals("NB_LIGNES_FIC") ? 1000 : TMP.TRT_MUTIX_PARAM_CODE.equals("TAILLE_FIC") ? 2000 : TMP.TRT_MUTIX_PARAM_CODE.equals("ID_HIST") ? 3000 : 0 

An update to the zero value can not occur because of the use of the Update_Flag as a filter on the output side.

The tMap output then connects to your DBOutput component using "update" as the "action on data" setting.

Best regards,

Thomas