ODBC Update query is giving error - SSIS
I have configured the Execute SQL task in SSIS with ODBC ThoughtSpot connection. I'm getting this error when I'm writing the query manually. This is the similar error I'm getting while performing delete operation.
update "schemaname"."tablename" set "field_varchar" = 'notupdated' where 1=1;
[Execute SQL Task] Error: Executing the query "update "schemaname"."tablename" ..." failed with the following error: "[ThoughtSpot][SQLEngine] (31480) syntax error near 'update "schemaname"."tablename" set "field_varchar" = 'updated' where 1=1;<<< ??? >>>'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
At the moment our ODBC driver does not support the UPDATE statement or DELETEs with a where clause - it's used primarily for insert operations and we support an internal UPSERT (update rows where primary keys match) - there are a few details here:
I believe that we will soon be providing an update to ODBC/JDBC capability to address this but can't give you a specific timescale.