Alteryx Update statement to PostGres

[MartinJacobsen]

I am trying to send an update statement to PostGres, with a dynamic substitution in the syntax, as I have done before with "regular" MS SQL.

So, I took a delete syntax from another MSSQL that work nicely (see attachment) with the query in the input data source:

SELECT 1 as Deleted;
delete from Data.Responses where ResponseId in (RESPONSEIDSREPLACE);

And then a "Modify SQL Query" replacing the string RESPONSEIDSREPLACE with a concatenated string holding the relevant IDs.

Works nicely with MSSQL. So I took it and reworked a bit to use for PostGres:

SELECT 1 as Update;

update "Input"."SurveyTransactions" set "InitStatusId" = '2' where "InitStatusId" = 'STATUS';

Where STATUS being replaced with my concatenated string of relevant IDs.

This fails though, with the errror:

Dynamic Input (111) Error SQLPrepare: [Simba][PostgreSQL] (30) Error occurred while trying to execute a query: [SQLState 42601] ERROR: syntax error at end of input¶LINE 1: SELECT * FROM ¶ ^¶

Seems like PostGres needs me to specify a table instead of a query?

So, I tried pointing to the specific table here, and then moved my syntax to the "Post SQL Statement" field instead, but then it seems the replacement of my string (STATUS) doesnt work

(Error occurred while trying to execute a query: [SQLState 22P02] ERROR: invalid input syntax for type integer: "STATUS")

...so, how do you people dynamically send updates (or delete statements) to PostGres?

[gabrielvilella]

Have you tried using the IN syntax rather than just the =? Like "InitStatusId" IN ('1234','5678')

[MartinJacobsen]

Yes, that was my original syntax, since I wanted to update via a concatenated string of Ids (Update table set InitStatusId = '2' WHERE Ids IN ('aa','bb','cc'))

That failed as well.

When the syntax was in the "Table or Query" field I get the "ERROR: syntax error at end of input¶LINE 1: SELECT * FROM ¶ ^¶" because Alteryx can't handle a syntax other than SELECT * FROM for PostGres SQL here

Moving the syntax to the "Post SQL Statement" field gave me the error that the field were not replaced with my concatenated string: "ERROR: column "replace" does not exist¶LINE 1: ...set "InitStatusId" = '2' WHERE "TransactionKey" IN (REPLACE)¶ ^"