1

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.

  • Do you have "SubscriberKey" turned on in your account? Also, is this a sendable data extension? – user8169 May 20 '14 at 14:24
  • 1
    Please post your AMPscript, including the few lines before the UpsertData function call. – Timothy May 20 '14 at 14:29
  • yes and it is also a sendable DE... – Jack85 May 20 '14 at 14:30
  • added above : if you feel needed few more above this then i can post some more script.... – Jack85 May 20 '14 at 14:40
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.

  • You are Awesome !!!!!!!!!!! thxxxxxx – Jack85 May 20 '14 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.