unwanted creation of duplicate rows using Tmap and PK to match/update

[GNOLE]

Hello world,

I have a problem about a very distinct situation :

I Have 2 tables, let's call them Addresses and Clients;
I want to copy the rows in Addresses concerning Client A to Client B and according to the situation, insert or update any new info. Classic, right.

Normally, I would use Addresses as a target and source of information, join Addresses on Clients, and try to match with the target Addresses table.
How I would do that would be to tick every interesting column in target table as Primary Key and let the mapping execution take its course.

This has bin so far so good but in that particular case, any checking is ineffective and all the rows matched by the source are copied to the target table.
If I execute the mapping twice, I will have twice as many addresses created, which is  illogical.

The target table options are 'Update OR Insert', but I also tried with 'Insert if not exist' with the same outcome.


Maybe I use Primary Keys poorly, or is there anything I am missing?

BTW I use 5.2.2 Talend Open Studio for Data Integration.

Any Ideas?

[Anonymous]

Can you take screenshots of your configuration? Ideally that would include a screenshot of your tMap config, the "Basic settings" and "Advanced settings" tabs of your DB output component.

[GNOLE]

Thanks for your interest.
here is a screenshot of the mapping.




Other than the target being 'Update or insert', there is nothing fancy somewhere else.
The SQL request is functionnal as well. Tested directly with the DataBase.

Any Ideas?

[Anonymous]

I'm afraid the screenshot is not visible. This might be because you have not posted enough yet. Can you tell me, have you specified an "update key" in the Advanced Settings tab on your database output component? You will need that if you are using insert or update. To get that option, click on the "Use Field options" tick box in the "Advanced Settings" tab. 

[GNOLE]

Thanks,
I do not think it to be necessary because there is one field for this purpose.
It is a technical field filled with a string that I create and should help me not recreate the same line.

Let me explain;
After multiple tries, I changed the way things are matched all the way around to this:

client A
-An address is created 
-The minimum information required (asside from mail information and the likes) is an account ID (ext. key) and an address type
-I insert the string in the technical field as such : <Type of address> '_' <Account ID> 

Client B
-Client A and B are matched
-The technical field and account ID are matched so that the copy happens ONCE from lv1 to lv2

I still don't get why lines keep adding up .

If you have any thoughts...


looking forward to read from you.

[Anonymous]

If you are getting records inserting when they should be updating, it is almost certainly down to the "Field options" on the "Advanced Settings" tab of your database output component. Unfortunately, without seeing this it is difficult to give more advice.