topic Re: How update with where clause. in Talend Studio

How update with where clause.

Anonymous — Fri, 23 Jan 2015 13:34:09 GMT

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.

Re: How update with where clause.

Anonymous — Fri, 23 Jan 2015 13:38:53 GMT

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

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

Re: How update with where clause.

Anonymous — Fri, 23 Jan 2015 14:00:32 GMT

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.

Re: How update with where clause.

Anonymous — Fri, 23 Jan 2015 14:19:48 GMT

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.

Re: How update with where clause.

Anonymous — Mon, 26 Jan 2015 14:05:57 GMT

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.

Re: How update with where clause.

Anonymous — Mon, 26 Jan 2015 20:32:14 GMT

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:

Re: How update with where clause.

Anonymous — Thu, 07 Apr 2016 11:24:47 GMT

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!!!