topic Re: Update or Insert without a primary key in Talend Studio

Update or Insert without a primary key

Anonymous — Sat, 16 Nov 2024 13:53:12 GMT

Hi all,
The jpb I am designing aims to collect data from a SQL Server database and then store them into a MySQL database periodically.
So the main architecture of this job :
- Iterate through a collection of fields in my local MySql database.
- For each field of the Mysql data, execute a query on a remote SQL Server database (passing the field as param)
- Mapping the collected data with a local MySQL table
- Write the result in a local table on the MySQL database : If the row exists it is updated, if not it is added.
The problem is in the last step (saving the data), the existence check in my case has to be based on some table fields (date, hour, campaign ...) which are not primary keys.
How this can be done ? I need to check if the row corresponding to the retrieved data row (from MSSQL) exists is the MySQL table (according to date, hour, campaign ...) if exists update some needed fields, if not insert a new row with the retrieved data
Thanks to all
Nacef

Re: Update or Insert without a primary key

Anonymous — Tue, 07 Jul 2009 07:03:02 GMT

Hello Nacef
When you define the output schema, select one of columns as key column. As there must exist one key column if you do 'update' action.
Best regards

shong

Re: Update or Insert without a primary key

Anonymous — Tue, 07 Jul 2009 10:29:37 GMT

Hi all,
I resolved it using the "use field options" from the advanced settings of the tMySQLOutput object and then selecting the columns of the schema to be the key for the update.
This worked like a charm.
Nacef

Re: Update or Insert without a primary key

_AnonymousUser — Wed, 17 Aug 2011 21:06:16 GMT

Cn you plese give exmple or screen shot for this? In my cse, updte is not hppening.

Hello Nacef
When you define the output schema, select one of columns as key column. As there must exist one key column if you do 'update' action.
Best regards

shong

Re: Update or Insert without a primary key

Anonymous — Thu, 18 Aug 2011 14:48:59 GMT

@Pkhedkr07
You can just enable a column the schema of any of your component. Have a look at my screenshot. Where ColumnA is the key because the key column is enabled in the schema.

Regards

Re: Update or Insert without a primary key

_AnonymousUser — Tue, 30 Aug 2011 18:25:44 GMT

Hello Nacef
When you define the output schema, select one of columns as key column. As there must exist one key column if you do 'update' action.
Best regards

shong

What if i need to insert and update, and need to set my primary key through sequence through toracleoutput. how can i do it.

Re: Update or Insert without a primary key

Anonymous — Tue, 30 Aug 2011 20:40:40 GMT

I am using tELTOracleOuptput along with other tELT component. I wish to update one of the column a table using tELTOracle output. I am describing my problem below
I have a table named "room". It has three columns namely X,Y & Z where x is a unique column. The content of the table is given below
X Y Z
12 23 34
23 14 15
34 12 23
I want to update the column Y of the "room"(tELTOracleInput) table based on the joining with another table namely "house"(tELTOracleInput), which has two columns namely Z & Y. The content of the table "house" is given below.
Z Y
15 39
34 25
23 11
I have joined(left outer join using tELTMap ) table room with table house with the joining condition being room.Z=house.Z(+) and populating house.Y
Now i want room.Y to be updated to house.Y
The final table "room" should be
X Y Z
12 25 34
23 39 15
34 11 23
Can anyone help me out how to use the update in tELTOracleOutput to get the desired result.
Note:- Data type of all the columns is varchar(50). I am using TDQ .

Re: Update or Insert without a primary key

Anonymous — Wed, 31 Aug 2011 02:10:16 GMT

Hello Nacef
When you define the output schema, select one of columns as key column. As there must exist one key column if you do 'update' action.
Best regards

shong

What if i need to insert and update, and need to set my primary key through sequence through toracleoutput. how can i do it.
Hi
Please see this topic to know how to call a Oracle sequence on through tOracleOutput.
https://community.talend.com/t5/Design-and-Development/resolved-Oracle-sequence-while-insert/td-p/64968
Best regards
Shong

Re: Update or Insert without a primary key

Anonymous — Wed, 31 Aug 2011 02:11:49 GMT

I am using tELTOracleOuptput along with other tELT component. I wish to update one of the column a table using tELTOracle output. I am describing my problem below

Hi
For better management, please report a new topic and then I will answer you on the new topic.
Thanks for your support!
Best regards
Shong