topic Re: action on date : Update or insert adds all the rows to mySQL table in Talend Studio

action on date : Update or insert adds all the rows to mySQL table

Anonymous — Thu, 17 Sep 2015 11:41:48 GMT

When you use databases , probably you may want to insert new rows and update existing ones right?
Insert, insert/update, update/insert it doesn't matter, whenever I run the job it adds all the records again to the database.
How can i solve this issue?
MySQL table : Talend: insert/update
Talend : Advanced Settings MySQL duplicated data : Best Regards
Cihan

Re: action on date : Update or insert adds all the rows to mySQL table

Anonymous — Thu, 17 Sep 2015 16:03:20 GMT

hi,
are you using auto-increment for the table's Id ?
regards

Re: action on date : Update or insert adds all the rows to mySQL table

Anonymous — Mon, 21 Sep 2015 07:21:43 GMT

YES at msSQL Is identiy > Yes , Identity increment > 1
Please help, I have 30 tables, Each day i am deleting every table's data manually and running the talend jobs again not to have duplicated data.
Regards
Cihan

Re: action on date : Update or insert adds all the rows to mySQL table

Anonymous — Wed, 23 Sep 2015 11:38:08 GMT

if you're using auto-increment, do not map that key in Talend.
have a look at that post about that :
http://bekwam.blogspot.fr/2011/05/mapping-auto-increment-column-in-talend.html
But ... , if you haven't got a key, you cannot update data ... So do not use 'insert or update' option, but use 2 différents flows.
one for new data => insert option on table
one for update data => update option on table
it's a better way 'cause update or insert (& reverse) are full scan.
For that purpose, use a tmap with your data input, a lookup to retrieve all id already in your table (if it's a very huge table use option reload at each row with the globalmap trick in the xherre clause).
join input & lookup flow (inner join)
first output for update , second output for create (insert) using the tmap option 'catch lookup inner join reject' (that mean no id matche so it's a new row)
that's it
hope it help
regards
laurent