topic How to execute Update query with where clause, and select statement in where clause in Talend Studio

How to execute Update query with where clause, and select statement in where clause

Ramaraju2 — Sat, 16 Nov 2024 00:50:17 GMT

Hi All,

I have a scenario , update query with 'WHERE' clause, inside where clause also need to get value using SELECT query. I doing this using tDBRow, but still stuck at how to get value from SELECT statement with tDBRow.

SELECT statement is not working inside where clause

Here is example:

"UPDATE table_A

SET DT1= ? ,

DT2=?,

column2= ?,

column3= ?

WHERE column4= ? AND

(column2 is null OR DT2=? ) AND

DT3 = (SELECT MAX(DT3) FROM table_A WHERE column1=? ) "

Re: How to execute Update query with where clause, and select statement in where clause

Anonymous — Wed, 13 Jan 2021 03:36:09 GMT

Are there always multiple rows read from the source file? You need to iterate each row so as to you are able to access the current value of columns.

Re: How to execute Update query with where clause, and select statement in where clause

Ramaraju2 — Wed, 13 Jan 2021 03:42:07 GMT

only 1 row in each file.

Re: How to execute Update query with where clause, and select statement in where clause

Anonymous — Wed, 13 Jan 2021 04:58:22 GMT

If there is only one row in each file, you can build the query string on tJavaRow and execute the query on tDBRow, eg:

tFileList--iterate--tFileInputDelimited--tMap--out-->tJavaRow--oncomponentok--tDBRow

on tJavaRow:

context.query="update tableName set fieldName1="+input_row.column1+" and fieldName2="+input_row.column2

//context.query is a context variable, string type.

//to access the value of column from input row using expression: input_row.columnName

on tDBrow:

set the Query field with context variable as:

context.query

Hope it help you!

Regards

Shong

Re: How to execute Update query with where clause, and select statement in where clause

Ramaraju2 — Wed, 13 Jan 2021 07:20:18 GMT

Hi Shong,

Thanks for your answer. I tried it with simple update query with hardcoding values and with input row values. still no luck. Getting error " row1 cannot be resolved to a variable" at tJavaRow component. Please help me

Context Variable declarationFlow

context.query in tJavaRow

Query in tDBRow

Re: How to execute Update query with where clause, and select statement in where clause

Anonymous — Thu, 14 Jan 2021 08:19:56 GMT

can you share the code of tJavaRow? I see some code reference to row1

Re: How to execute Update query with where clause, and select statement in where clause

Ramaraju2 — Sat, 16 Jan 2021 01:23:10 GMT

only this update qury i am using tJavaRow

Re: How to execute Update query with where clause, and select statement in where clause

Ramaraju2 — Sat, 16 Jan 2021 01:24:04 GMT

only this update query , using in tJavaRow

Re: How to execute Update query with where clause, and select statement in where clause

Ramaraju2 — Thu, 21 Jan 2021 23:50:58 GMT

Hi Sheng,

Plesase help me out this.