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.
Senior Analytics Engineer