How update with where clause.

[Anonymous]

Hi,
I have a little problem, I need update a table with a where clause, but I don't know what the best component to make it.
I have this query:
UPDATE SGPE_RE_INSTALATION SET VALUE =AA
, USER = 9999999998
, ID_REGISTER =12345
 WHERE ID =   ABC
I try with mysqlOutPut and mysqlRow, but I don't know how use the "where".
Thank you.

[Anonymous]

You should put the '' between your value.
Try : 

UPDATE SGPE_RE_INSTALATION SET VALUE ='AA'
, USER = '9999999998'
, ID_REGISTER ='12345'
 WHERE ID =   'ABC'

[Anonymous]

Sorry, I don't explain me very good, this query was only a example, It is not important whether this correct or not.
I see that mysqloutput have the option, in "Action on data" for Insert or Update. I want know if is possible use this component for an update query with a where clause or if I only can use the tmysqlrow component or if exist other component for update.

[Anonymous]

Oh, ok.
Actually, I am using a UPDATE with a clause WHERE with the tMySQLRow.
You can't configure the query with the tMysqlOutput.
The query will be the "Action on data", and the flow that is coming to the component.

[Anonymous]

Yes, the data will come from an 'input' component, and the output component will automatically do what you tell it via the 'insert or update' or whatever you tell it.
However, the question I think you are trying to ask is: How do I select all the data I will NEED to update (from the input component)?
You can do this via a simple where clause in your input component, though for this to work as I will say in a moment you will need both the input table and the ouput table on the same database, if this is not so I have an idea to help with that also but it is a little more difficult and complicated so I will not say now.
As for the sql query, here is my example using tables one and two with columns id, alpha, beta and delta:
select one.id
     ,one.alpha
     ,one.beta
     ,one.delta
from one,two
where one.id = two.id
and (two.alpha <> one.alpha
or two.beta <> one.beta
or two.delta <> one.delta)
;
This code would only select data needed to be updated, you would then pass this data into the output component with 'Update' selected and it would do its thing
I hope this helps.

[Anonymous]

There's a much easier way to do what you want than what V Pem recommends. 

Set your db output components "Action on Data" to "Update" 

On the Component->Advanced Settings sub-tab check the "Use field options" checkbox. In the Field Options table, the "Update Key" columns will be used to locate the records to update. ( analogous to the WHERE clause of an update query)

The "Updatable" checkbox will control which columns are applied ( like the SET clause ) 

Once these are set, Talend will generate update queries using the values inside the Talend Row. Let us know if you need any further assistance, we're always happy to help  :cool:

[Anonymous]

Thank you very much  JohnGarrettMartin for your explanation, I have used:

tBInput (from Sql Server, table1), tMap and tMySqlOutput (to MySql table2), and 
mapped with  tMap 
field table1.from1 with  table2.to1,
field table1.from2 with  table2.to2
then, with  tMySqlOutput 
configured "Update" as "Action on Data"
and
table2.to1  as " "Update Key"
table2.to2 as " Updatable"
obtaining something like ....
update table2 set table2.to2 = table1.from2 where table2.to1 = table1.from1
And WORKED!!!