topic Re: Update with like condition in MySql in Talend Studio

Update with like condition in MySql

Virginie2905 — Thu, 16 Nov 2017 09:09:50 GMT

Hi,
I would like to execute the following query, in MySql database:

UPDATE table1 SET COL1=new_data_1, COL2=new_data_2 WHERE COL1=old_data_1 AND COL2=old_data_2 AND COL3 like '%-data_3-%';

I have several requests upstream that allow me to obtain the necessary data.
I dont't know
- how to implement the "like" condition
- how to update the same data of the "where" condition (set COL1 =... WHERE COL1=...)

how could I do that?

Thanks!

Re: Update with like condition in MySql

Anonymous — Thu, 16 Nov 2017 10:09:49 GMT

The easiest way to do this would be to use a tMySqlRow component and write the UPDATE statement yourself. Lets say you have a component with the following columns feeding into the tMySqlRow....

row1.column1

row1.column2

row1.column3

row1.column4

row1.column5

(These are all Strings for ease of explanation).

In your tMySqlRow you can write the following query to carry out the update with the LIKE ....

"UPDATE table1 SET COL1='"+row1.column1+"', COL2='"+row1.column2+"' WHERE COL1='"+row1.column3+"' AND COL2='"+row1.column4+"' AND COL3 like '%"+row1.column5+"%'"

Take note that this is a bit of Java to build an UPDATE statement. The Talend columns are supplied as variables to the rest of literal String. Note the single quotes I have wrapped around the Talend column values. This is because in my example I am assuming them all to be Strings.

You should note that I have literally just written this off the top of my head and have not checked it. But this method will work as long as you build legitimate SQL.

Re: Update with like condition in MySql

Virginie2905 — Thu, 07 Dec 2017 16:15:54 GMT

Hi,

that's what I started to put in place, your answer confirmed it.

Thank you very much