topic Re: how to update rows with tMysqlOutput ? in Talend Studio

how to update rows with tMysqlOutput ?

Anonymous — Sat, 16 Nov 2024 14:37:24 GMT

Hi all,
sorry if this question has already been posted, or if a documentation exists : I don't find it.
I don't see, and don't understand how to make an update on MySQL datas... the SQL request would be :
update tableA set flag=0
where NOM in (select NOM from tableB).
So the tables A and B must be linked in any way.
I have tried some things but none works.
Thank you for responses.

Re: how to update rows with tMysqlOutput ?

Anonymous — Fri, 23 Mar 2007 14:33:07 GMT

In TOS 2.0.x, tMysqlOutput doesn't build this kind of query. The update queries follow this template:

update users
  set firstname = 'Pierrick', lastname = 'LE GALL'
  where id = 2

"id" is a key column in the schema, "firstname" and "lastname" are simple columns.
You can do the specific query you ask with a tMysqlRow.

Re: how to update rows with tMysqlOutput ?

Anonymous — Sun, 25 Mar 2007 11:07:39 GMT

OK it's clear when you know that simple columns of output are put in the "SET" part of query, and the key columns in "WHERE" part.
In fact, my query can indeed be realized with a tMap component, I test it and it works :
it is like this in the tMap :
- the "(select NOM from tableB)" is simply the input table
- the "update tableA set flag=0" is the output table with column "flag" (not a key !) force to "0"
- the "where NOM in " part is a link between column NOM of output (a key !) and column NOM of input.
then the tMap build queries like you say for each value of tableB.
Thank you for help.

Re: how to update rows with tMysqlOutput ?

Anonymous — Wed, 28 Mar 2007 14:21:31 GMT

A precision :
it works only if input table is different of output table, if not it fall in dead lock between the SELECT of tDBInput and the UPDATE of tDBOuput on same rows...
But it works if another component (like a tAggregateRow after the tDBInput) "break" the flow and free the locks of the SELECT before the UPDATE comes.

Re: how to update rows with tMysqlOutput ?

Anonymous — Tue, 24 May 2011 07:57:58 GMT

I got the problem described in the previous post: when I try to update data in the row selected by tMysqlInput writing it to MySQLOutput through tMap the job hangs.
When I simply insert a tAggregateRow between input and output it does not help. I tried different intermediate components like tReplace, tConvertType, tSleep but nothing changes.

Re: how to update rows with tMysqlOutput ?

Anonymous — Tue, 24 May 2011 09:05:01 GMT

I found a solution to my problem. tJavaRow can be used to separate SELECT from UPDATE. Table consists of 3 fields col1, col2, col3. (col1, col2) is a key. col3 value need to be changed from 0 to 1. code inside tJavaRow looks like:
Long col1, col2; //temp variables for input row key fields
col1=input_row.col1; // this field is a part of key
col2=input_row.col2; // this field is a part of key
output_row.col1 = col1; //this field is not changed
output_row.col2 = col2; //this field is not changed
output_row.col3 = 1; //this is the field I want to update