[resolved] tMap left outer join to insert/update and delete records

[Anonymous]

Hi,
I have one problem about delete record from table. In my job, I take tAccessInput do left outer join via tMap with tPostgreSQLInput (I need to update some Access column with postgres table column). I want to update or insert row that exist in tAccessInput to Postgres table and to delete record that doesn't exist in Postgres table.
Can you tell me how to do it ? I read some posts that we can create two different outputs in tMap to tPostgreSQLOutput which one performs insert/update and the other output performs delete. Bud I don't know how to do this with left outer join.
Best regards,
Sophanna

[Anonymous]

Hi Sophanna,
Any specific reason for using left outer join?
- Perform Main input inner join with lookup using tMap
- You will get reject rows from Main (need to configure in tMap output configuration)
- All these rejected records are update, or insert
- Your update and insert for records can be managed by the database, you don't need separate output from tMap,
Use another tMap and reverse main and lookup to get the deleted records, use subsequent sql query to delete records from target.
Check the forum post for further details.
https://community.talend.com/t5/Design-and-Development/resolved-Insert-Update-with-tMap/td-p/58028
Check tMap example
https://help.talend.com/search/all?query=tMap&content-lang=en
Thanks
Vaibhav

[Anonymous]

Hi Vaibhav,
Thanks for your quick reply. The reason that I need to use left outer join is MS access is user record data local DB (user can add new record, update existing record or delete record). tAccessInput and tPostgreSQLInput has different schema, so I have to use tMap (some columns from access and some from postgres). Therefore, I can't just delete data from postgres first then insert new data to it.
According to this scenario, could you help me by giving other ideas how to do solve this?
May I ask if I do inner join, then there is reject row, where these reject row comes from? is it from main or lookup?

[Anonymous]

Reject rows comes from Main.
Above scenario may satisfy your problem... try some POC.
Vaibhav

[Anonymous]

You gave me idea on how to do this. And this is what I come up with :
Access INNER JOIN postgres------->get exist in both table update to postgres.
Postgres INNER JOIN Acess-------->reject row then delete all these rows from postgres.
Access INNER JOIN postgres------->reject row then insert to postgres.
This is what you mean?
Best regards,
Sophanna

[Anonymous · Accepted solution]

Access(Main) INNER JOIN postgres (lookup)------->get reject (update and insert to postgres)
Another tMap different flow subjob
Postgres (Main)INNER JOIN Acess (Lookup)-------->reject row then delete all these rows from postgres.
Vaibhav

[Anonymous]

Hi Vaibhav,
You're brilliant. Thanks you for sharing.
But one more thing is that in first sub job, we will get only the row that doesn't exist in Postgres , then insert it to postgres. And I think we still need one more subjob which will update the data that both exist in these two table.
Best regards,
Sophanna

[Anonymous]

Hi Vaibhav,
I don't know how to retrieve rejected row. Could you take a look at my screenshot and tell me how to get rejected row?
Best regards,
Sophanna

[Anonymous]

You have to enable output reject in tMap configuration....
we still need one more subjob which will update the data that both exist in these two table.
>> This can be handled by the database insert/update
check out following tutorial.
http://www.talendforge.org/tutorials/tutorial.php?language=english&idTuto=9
Vaibhav

[Anonymous]

Hi,
May I ask, beside map these two table with primary key, do I need to give another criteria to get the rejected row that doesn't match this join ? Could you tell me what is "catch lookup inner join reject means"?
Best regards,
Sophanna