Update query on MS SQL Server database using Alteryx

Question

HI,

I want want to update one SQL table using Alteryx.

I want to use following query to update the table:-

update table

set Column1 = "1"

where Column1 = "2"

i have created the DSN as well TEST_DB but i dont know how to implement the update query.

Could you please share the example or suggest.

Thank you in advance.

alt_tush · Answer

Hi RolandSchubert,

Thank you for your prompt reply.

I tried as per your suggestion but it gives me error "primary key required for update option"

I tried with ODBC as well as OLEDB database connectivity but facing same error.

I tried with simple dummy table where only one column dummy and applied the update query but got an error

I used the following simple update query.

update [DATABASE].dbo.dummy
set [DATABASE].dbo.dummy.[dummy] = '1'
Where [DATABASE].dbo.dummy.[dummy] = '2'

Can you please help.

Thank you again 🙂

RolandSchubert · Answer

Hi @alt_tush ,

you can connect an Text Input tool with at least one column named like one column in your table (e.g.  [Column1]) with no records to an Output Data tool. In the Output Data tool, select the table you want to update and  "Append Existing" as Output Option. Use your update statement as  "Pre Create SQL Statement". The statement will be executed without adding or deleting any records.

Let me know if it works for you.

Best,

Roland