how to update rows with tMysqlOutput ?

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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