primary key required for update option - while execute the update query

[alt_tush]

Hi All,

I am getting "primary key required for update option" error while execute the update query using input tool.

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.

[PeterA1]

Hi @alt_tush, in order to use Update on the output you will need a primary key to be mapped on the database side. That is so when new records get sent, the Database "knows" which ones have changed and which ones are being updated or upserted. Hope this helps!

[Treyson]

Hey @alt_tush 

If you are unfamiliar with what this means, With RDBs there is a function called a primary key which notes a single field as the unique value to identify each record. Sometimes there are composite keys which is a primary key based on multiple fields. The table has to be constructed with Primary Keys. 

Here is an older Alteryx post about this: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Create-Database-Table-Primary-Key-i...

Here is some reading, specific to SQL Server but the concept applies to a bunch of stuff: https://www.w3schools.com/sql/sql_primarykey.ASP

[Treyson]

dang, I should have read into your original question a bit more. You understand updates so I guess you probably also understand PKs. Let me ask a few questions.

1) that Update statement. Are you putting it in the Pre/post SQL? 

2) Are you still trying to run that update when you have the "update; insert if new" function on? If so, it's erroring out because of the lack of a PK before you even get to your update statement.

3) How familiar are you with SQL? The "Update;Insert if new" function in Alteryx is wicked slow. essentially what happens is that it is creating a merge/insert statement for each record. So if you have a ton of records, it takes FOREVER. I would say my bread and butter these days when it comes to Alteryx/SQL/Snowflake (whatever RDMBS) is to write from Alteryx to a temp table and then run your own merge/insert once it's populated. Save that merge/insert as a stored procedure and call it from the Post SQL. It's a little bit of a pain to put together up front, but it's super fast because you are utilizing the best parts of SQL Server for updating records. The other benefit is that you now have a copy of all the records that were updated/inserted in a holding table so you can audit if you need. I can explain this more if you like.

[bertal34]

@Treyson 

Hello!  I just created a new discussion because I'm struggling getting the configuration figured out for my use case.  I put the link below.  It sounds like your method of creating a temp table followed by a stored procedure might be best.  You are talking about creating the temp table on the SQL server correct?  What tool is best to use, Output Data or maybe an In-Db tool?  Can the update be done in same tool as temp table creation?  Will an update sql statement work instead of store proc?  I would appreciate any feedback!  Thanks.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Update-Fields-in-SQL-Server-Ta...