Talend community forum / Update or Insert without a primary key

nacef · 2009-07-06 16:24:30

Tags: [database, insert, mssql, mysql, update]

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

shong · 2009-07-07 08:03:02

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

nacef · 2009-07-07 11:29:37

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

Pkhedkr07 · 2011-08-17 22:06:16

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

shong wrote:
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

mpa · 2011-08-18 15:48:59

@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

Therese · 2011-08-30 19:25:44

shong wrote:
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.

y2k_k2a · 2011-08-30 21:40:40

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 .

shong · 2011-08-31 03:10:16

Therese wrote:
shong wrote:
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.
http://www.talendforge.org/forum/viewtopic.php?id=1488

Best regards
Shong

shong · 2011-08-31 03:11:49

y2k_k2a wrote:
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

Announcement

Unanswered posts

#1 2009-07-06 16:24:30

Update or Insert without a primary key

#2 2009-07-07 08:03:02

Re: Update or Insert without a primary key

#3 2009-07-07 11:29:37

Re: Update or Insert without a primary key

#4 2011-08-17 22:06:16

Re: Update or Insert without a primary key

shong wrote:

#5 2011-08-18 15:48:59

Re: Update or Insert without a primary key

#6 2011-08-30 19:25:44

Re: Update or Insert without a primary key

shong wrote:

#7 2011-08-30 21:40:40

Re: Update or Insert without a primary key

#8 2011-08-31 03:10:16

Re: Update or Insert without a primary key

Therese wrote:

shong wrote:

#9 2011-08-31 03:11:49

Re: Update or Insert without a primary key

y2k_k2a wrote:

Board footer