Update or Insert without a primary key

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[_AnonymousUser]

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

[Anonymous]

@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

[_AnonymousUser]

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.

[Anonymous]

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 .

[Anonymous]

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/64...
Best regards
Shong

[Anonymous]

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