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

[Ramaraju2]

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=? ) "

[Anonymous]

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.

[Ramaraju2]

only 1 row in each file.

[Anonymous]

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

[Ramaraju2]

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

 

[Anonymous]

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

[Ramaraju2]

only this update qury i am using tJavaRow

[Ramaraju2]

only this update query , using in tJavaRow

[Ramaraju2]

Hi Sheng,

Plesase help me out this.