how to SET and DELETE using a single UpdateExpression with Dynamo

Question

I'm trying to update an item using updateExpression, and i'd like to edit an attribute and delete another attribute on the same object, but i get the following error:

Invalid UpdateExpression: Syntax error; token: EOF near: "attributeToDelete"

I have to admit i wrote the update expression guessing the proper way to use it (failing). Here's the code snippet:

dynamodb.updateAsync({
    TableName: `myTable`,
    Key: { id: req.params.id },
    UpdateExpression: 'SET attributeToEdit = :newValue DELETE attributeToDelete',
    ExpressionAttributeValues: { ':newValue': 'valueToSet' },
  })

How am i supposed to write an UpdateExpression when I want to include more than a action? (where actions are -> SET, DELETE, REMOVE, ADD)

score 73 · Accepted Answer · answered May 17 '17 at 11:02

73

Use REMOVE rather than DELETE. DELETE is used to delete element from SET.

 UpdateExpression: 'SET attributeToEdit = :newValue REMOVE attributeToDelete',

REMOVE - Removes one or more attributes from an item.

answered May 17 '17 at 11:02

notionquest

37,595
6
111
105

26

My experiance was I put a comma before REMOVE. Therefore my expression FAILED. So don't put DONT PUT COMMA. :) – Thiwanka Wickramage Apr 03 '20 at 05:56
1

Also, the syntax parser will "blame" `REMOVE` when the problem was the `,` (because it was in list parsing mode for the set operation... The more you know!!) – lol Jul 11 '21 at 06:35

score 0 · Answer 2 · answered May 18 '18 at 15:49

I struggled with this for a long while, so I figured a second example may be helpful for future searchers. This example is using CakePHP, Marshal JSON, SET and ADD, and includes variables to show a couple of different ways to get what you want. You'd obviously also pass in all the variables in the overall function calling this.

    $key = $this->marshaler->marshalJson('
        {
            "field_one": ' . $variableOne . '
        }
    ');

    if ($criteriaOne === null) {
        $add = $this->marshaler->marshalJson('
            {"0":{
                "field_two": ' . $variableTwo . ',
                "field_three": "' . $variableThree . '"
            }}
        ');
    } else {
        $add = $this->marshaler->marshalJson('
            {"0":{
                "field_two": ' . $variableTwo . ',
                "field_three": "' . $variableThree . '"
                "field_four": "null"
            }}
        ');
    }

    $eav = [
        ":vals" => [
            "L" => [$add[0]]
        ],
        ":vals1" => [
            "NS" => ["$variableTwo"]
        ]
    ];

    $ean = [
        "#co" => "column_one",
        "#ct" => "column_two"
    ];

    return [
        'TableName' => 'Dynamo_Table_Name',
        'Key' => $key,
        'UpdateExpression' =>
            'SET #co = list_append(:vals, #co) ADD #ct :vals1',
        'ExpressionAttributeNames' => $ean,
        'ExpressionAttributeValues' => $eav,
        'ReturnValues' => 'ALL_NEW'
    ];

Good answer and I upvoted. But here is a better answer all the same. https://stackoverflow.com/a/34186828/683178 — look, Sep 23 '19 at 22:33

how to SET and DELETE using a single UpdateExpression with Dynamo

2 Answers2