Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to achieve
tELTMSSQLInput -> tELTMSSQLMap -> tELTMSSQLOutput
and trying to use merge statement like in tELTOracleOutput
Any help on that? The update statement is completely wrong in tELTMSSQLOutput.
Hello,
What's problem are you getting? What does your where clause look like?
Could you please have a look at this related topic and let us know if it helps?
https://community.talend.com/t5/Design-and-Development/tELTORACLE-UPDATE/m-p/124292
Best regards
Sabrina
Hi @xdshi
Sorry for my quick non-informative question. Let me fix that.
What I'm trying to achieve is a very straight forward update all of TGT table from matching SRC table, which looks something like this:
The configuration inside the tELTMSSqlMap component looks like this:
When I run the job, it's creating a query like this:
Starting job testELTUpdate at 10:40 12/12/2018. Updating with : UPDATE ETL.TGT SET PK=(SELECT SRC.PK FROM ETL.SRC SRC),STD_NAME=(SELECT SRC.STD_NAME FROM ETL.SRC SRC),STD_NUM=(SELECT SRC.STD_NUM FROM ETL.SRC SRC) WHERE 1=1 Exception in component tELTMSSqlOutput_1 (testELTUpdate) com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1608) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:578) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:508) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:461) at etl_talend.testeltupdate_0_1.testELTUpdate.tELTMSSqlMap_1Process(testELTUpdate.java:1074) at etl_talend.testeltupdate_0_1.testELTUpdate.tDBConnection_2Process(testELTUpdate.java:1430) at etl_talend.testeltupdate_0_1.testELTUpdate.runJobInTOS(testELTUpdate.java:6088) at etl_talend.testeltupdate_0_1.testELTUpdate.main(testELTUpdate.java:5591) [FATAL]: etl_talend.testeltupdate_0_1.testELTUpdate - tELTMSSqlOutput_1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. com.microsoft.sqlserver.jdbc.SQLServerException: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:254) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1608) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:578) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:508) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:461) at etl_talend.testeltupdate_0_1.testELTUpdate.tELTMSSqlMap_1Process(testELTUpdate.java:1074) at etl_talend.testeltupdate_0_1.testELTUpdate.tDBConnection_2Process(testELTUpdate.java:1430) at etl_talend.testeltupdate_0_1.testELTUpdate.runJobInTOS(testELTUpdate.java:6088) at etl_talend.testeltupdate_0_1.testELTUpdate.main(testELTUpdate.java:5591) [statistics] disconnected Job testELTUpdate ended at 10:40 12/12/2018. [exit code=1]
Which doesn't make any sense to me.
Notice the update query it's creating:
UPDATE ETL.TGT SET PK=(SELECT SRC.PK FROM ETL.SRC SRC),STD_NAME=(SELECT SRC.STD_NAME FROM ETL.SRC SRC),STD_NUM=(SELECT SRC.STD_NUM FROM ETL.SRC SRC) WHERE 1=1
the "1=1" clause is added by me in output component, which is configured for update.
I hope I explained the problem much better. Please advise..
Edit: Using Talend Cloud Data Management Platform (7.0.1)
Hi @chamak if you found a solution feel free to share it