Write To Database - Update; Insert if New - Not Working

[regena]

I'm having trouble updating an existing record in a database using the standard output tool and I'm not sure what the error is.  A primary key is defined on the table and I can insert a record using this approach so I know it isn't permissions

Let's say the initial record in the database looks like the following

AddressID (primary key)	City	State	Zip
12345	[null]	NY	54321

I prepare my data to write and have it like this (to update the City)

AddressID (primary key)	City	State	Zip
12345	ThisTown	NY	54321

 

I'm using a the Update; Insert if New option and getting an error 'Duplicate Key violates unique constraint 'pk_addressID' key (addressID) = (12345) already exists

This is using a postgresql connection that I've used before and can't figure out whats wrong (connection string blanked out for this image)

 

[Sebastiaandb]

Hi @regena ,

When updating a record in a database we normally close our old records by using some extra DATA PK fields, like this:

1- The new record gets inserted (with same business PK but different date PK)

2- Old record gets closed as the date gets stamped by datetimenow() with the same business key as the new inserted record.

In our case we don't use Update; insert if new option. What i believe could be the problem with your case is that it somehow wants to insert your altered record in the database since its throwing you that unique constraint (happens when the key is already there). Firs thing you could try is to set that option to "Update; error on update failure; i hope by that, you force it to update the field instead of trying to insert it.

Greetings,

Seb

[regena]

I can give that a try but I dont' understand why its not trying the Update function.  if I was writing in SQL it would be something like 

update table
set City = "ThisTown"
where AddressID = 12345

Is there no way to do this? 

AddressID (primary key)	City	State	Zip
12345	ThisTown	NY	54321

[Sebastiaandb]

@regena ,

Should work. Maybe the following link helps?:

Solved: Output Data Tool - update, insert if new - Alteryx Community

[SoccerTil]

I agree with @Sebastiaandb , it should work. Be aware that Alteryx is deciding whether to do the insert or update, not the database. Assure that the field types in the database and Alteryx workflow match for the primary key field/attribute as this would cause Alteryx to assume it is a new record while the database will autoconvert the column data type.

[regena · Accepted solution]

Thanks both - the issue was actually related to the schema I was using.  Very strange & different than how I'm using it in my IDE. 

Either way I'm running a PreSQL "SET search_path TO [desired_schema];

Then I removed the schema from the output [desired_schema].tablename -> tablename and it worked !

[SoccerTil]

glad @regena you found your problem and solution. MySQL does some of the same strange default processing that needs to be worked around.

[samorod70]

I have problems with this Option Update; Insert if New in output tool. I get always in my workflow:
Output Data (32) ODBC Driver version: 03.80
Output Data (32) Lua Script Loaded: mysql.lua - DSN: Alteryx_Adverity_8.2
Output Data (32) UPSERT; You used the same data, no rows were updated/inserted.
Designer x64 The Designer x64 reported: You have found a bug. Die Pipe wurde beendet. ¶
The other options are working. Most time we use append option for writing.
Is it a bug in Alteryx?