topic Re: [resolved] tMap left outer join to insert/update and delete records in Talend Studio

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

Anonymous — Thu, 22 May 2014 12:37:21 GMT

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

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

Anonymous — Thu, 22 May 2014 13:01:28 GMT

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

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

Anonymous — Thu, 22 May 2014 13:20:54 GMT

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?

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

Anonymous — Thu, 22 May 2014 13:46:41 GMT

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

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

Anonymous — Thu, 22 May 2014 14:02:36 GMT

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

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

Anonymous — Thu, 22 May 2014 14:05:58 GMT

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

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

Anonymous — Thu, 22 May 2014 14:15:30 GMT

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

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

Anonymous — Thu, 22 May 2014 17:04:14 GMT

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

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

Anonymous — Fri, 23 May 2014 04:48:01 GMT

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

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

Anonymous — Fri, 23 May 2014 06:23:50 GMT

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

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

Anonymous — Fri, 23 May 2014 06:30:56 GMT

what is "catch lookup inner join reject means"?
Check this tutorial https://help.talend.com/search/all?query=tMap&content-lang=en for the description of "Catch lookup inner join reject"
Vaibhav

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

Anonymous — Fri, 23 May 2014 07:40:24 GMT

Hi,
Sorry for asking repeated question. Could you tell me 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.
Sophanna

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

Anonymous — Wed, 11 Feb 2015 07:29:36 GMT

Hi This is Prashant.I Have 10 Records Excel File.I am Converting it into Access File.And the Fields Names are First Name,Surname,Title, Gender,Year etc.Now I Want to Update Particular Row in Access File.For Example if the First Name is Smith i Want to Make it as John.Please Help me Regarding with this.

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

_AnonymousUser — Tue, 24 Jan 2017 13:11:57 GMT

Hi Team,
I am trying to do CDC using file system.
I have 2 input files(.csv) one for current day and one for previous day.
I wanted to compare these 2 files and get the Inserted,updated and Remains unchanged records into 3 different flows.
I tried it using tmap component but i m not able to acheive it.
can anyone help me with this?
Thanks,
Srinivas