0

I am considering using this endpoint:

https://developer.salesforce.com/docs/marketing/marketing-cloud/guide/putDataExtensionRowByKey.html

to insert and update (UPSERT) values.

Is it possible to use this endpoint to change the value of a column in an existing row if the column is part of the data extension's primary key?

Say, for example, that I have a Data Extension with these columns:

  • PrimaryKeyColumn
  • NonPrimaryKeyColumn

Where PrimaryKeyColumn is a primary key and NonPrimaryKeyColumn is not.

If I insert a row with with PrimaryKeyColumn = "foo", can I then subsequently update that row to change PrimaryKeyColumn to "bar"?

2 Answers 2

1

This is not possible with any API or anything else. It is due to the basic concept of data extension in SFMC. A primary key is used to identify a row uniquely in the data extension. If you change a value for the Primary key column it will simply insert a new record. The best way for you to achieve this is to completely remove the old rows and then insert new rows with the new primary key.

3
  • 1
    There are numerous database systems that allow changing the a value in a column that is part of a primary key. MySQL is just one example.
    – astletron
    Jan 18, 2022 at 14:34
  • Updated my answer :) Jan 18, 2022 at 15:08
  • Thank you for taking the time to update your answer. I hope my feedback has been more useful than confrontational! My experiments still do not support your conclusion. I have been able to change the value of columns in SF MC DEs that are part of the DE's primary key. See my answer for a more detailed example of what I mean.
    – astletron
    Jan 30, 2022 at 19:19
-4

This IS possible.

For example, you can create a row like so:

Host: https://YOUR_SUBDOMAIN.rest.marketingcloudapis.com
PUT /hub/v1/dataevents/key:KEY/rows/Email:[email protected]
Content-Type: application/json
Authorization: Bearer YOUR_ACCESS_TOKEN

{
    "keys": {
            "Email":"[email protected]"
             },
    "values":{
            "LastLogin":"2013-05-23T14:32:00Z",
            "IsActive":true,
            "FirstName":"John",
            "FollowerCount":2,
            "LastName":"Smith"
             }
}

and then change the Email value in the row like so:

Host: https://YOUR_SUBDOMAIN.rest.marketingcloudapis.com
PUT /hub/v1/dataevents/key:KEY/rows/Email:[email protected]
Content-Type: application/json
Authorization: Bearer YOUR_ACCESS_TOKEN

{
    "keys": {
            "Email":"[email protected]"
             },
    "values":{
            "LastLogin":"2013-05-23T14:32:00Z",
            "IsActive":true,
            "FirstName":"John",
            "FollowerCount":2,
            "LastName":"Smith"
             }
}

After the second PUT, there will be only one row and it will have Email of "[email protected]".

6
  • 5
    Some additional detail would likely help other people with the same or similar issues, please edit your answer to add more details.
    – Derek F
    Jan 9, 2022 at 19:44
  • 5
    Are you sure this is the case, and you don’t just add another row with a PK value of “bar”? Jan 9, 2022 at 20:21
  • 1
    yes, please add details. It shouldn't work, but tbh I wouldn't even be surprised if SFMC somehow allowed that to happen. Then it would be quite interesting how it handles newly introduced PK violations - say, if you managed to update, but you updated it to a PK value already present on some other record. Will you then have two records with the same PK? Will THAT break? Again - it shouldn't work, and if it does, I would exercise extreme caution. Jan 10, 2022 at 11:28
  • Updated with much more detail.
    – astletron
    Jan 18, 2022 at 14:29
  • Just did same test as you. When pushing similar payload to your last example {"keys": {"id":"4"},"values":{"email":"[email protected]","name":"Lukas"}} PUT to /hub/v1/dataevents/KEY/rows/id:5, I receive a response containing the old key value: {"keys": {"id": "5"},"values": {"email": "[email protected]","name": "Lukas"}}, which is confirmed by checking the DE in UI Jan 19, 2022 at 8:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .