ExactTarget : Violation of PRIMARY KEY constraint . Cannot insert duplicate key

Question

Am trying to update form values in to DE using UpsertData function. I could be able to update all form values expect Email address... which is not the primary key (customernumber).

If i keep the emailaddress as it is then it gets updated but when i change the email address and submit, i get the following error:

"Violation of PRIMARY KEY constraint 'PK_C102020224_6ea675c2-cd30-e311-8434-e83935a8ec2c'. Cannot insert duplicate key in object 'C102020224.DE'. The statement has been terminated." Exception Info: An error occurred when attempting to execute an UpsertData function call. See inner exception for details.

ExactTarget.OMM.OMMException: An error occurred when attempting to execute an update of the database. See inner exception for details.

Sql Text:

UPDATE [C10202064].[DE] SET [FirstName] = @p0_0,[LastName] = @p0_1,[Zip] = @p0_2,[Mobile] = @p0_3,[Phone] = @p0_4,[Country] = @p0_5,[custNO] = @p0_6,[Wants_email] = @p0_7,[Wants_sms] = @p0_8,[Add_Edit_Date] = @p0_9,[Optoutdate_sms] = @p0_10,[Optoutdate_email] = @p0_11,[Mobilecode] = @p0_12,[Locale] = @p0_13,[Signup] = @p0_14,[SubscriberKey] = @p0_17 
WHERE [emailaddress] = @p0_18;
IF @@ROWCOUNT = 0
    INSERT INTO [C10202064].[DE] ([emailaddress],[FirstName],[LastName],[Zip],[Mobile],[Phone],[Country],[custNO],[Wants_email],[Wants_sms],[Add_Edit_Date],[Optoutdate_sms],[Optoutdate_email],[Mobilecode],[Locale],[Signup],[SubscriberKey])
     VALUES (@p0_19,@p0_20,@p0_21,@p0_22,@p0_23,@p0_24,@p0_25,@p0_26,@p0_27,@p0_28,@p0_29,@p0_30,@p0_31,@p0_32,@p0_33,@p0_34,@p0_37);

Sql Parameters:..

Strange exception here ... in DE : custNO is the primary key but in the exception it shows Where email adreess = ....seems instead its taking email address as key??

Amp script :

SET @result = UpsertData("DE", 1, "emailaddress", @uemailaddress, "FirstName", @ufirstname, "LastName", @ulastname, "Zip", @uzip, "Mobile", @mobile1, "Phone", @phone, "Country", @ucountry, "custNO", @ucustNO, "Wants_email", @uemailopt, "Wants_sms", @usmsopt, "Add_Edit_Date", @editdt, "Optoutdate_sms", @smsoptoutdt, "Optoutdate_email", @emailoptoutdt, "Mobilecode", @countrycode, "Locale", @ulocale, "Signup", @signupdt,  "SubscriberKey", @friendkey)

thanks in advance.

Answer 1

The issue lies in UpsertData("DE", 1, "emailaddress", ...

What UpsertData is doing is building the WHERE clause. The "1" tells how many fields following the "1" will be in the WHERE clause, the remaining fields would be updated. If you had a "2", then emailaddress and firstname would be the WHERE clause, and the remaining fields would be updated. You want that WHERE to be something unique, like a primary key, or a unique set of values. Swap out custno with emailaddress.