Perform DynamoDB Conditional Update (Using a Visual Editor)

DynamoDB lacks an equivalent of SQL-ish UPDATE X WHERE Y. Despite supporting PartiQL, a query language very similar to SQL, that operation is still not possible because of how DynamoDB works - you cannot update multiple rows at once, you can only run putItem / updateItem on a per item basis.

Does it mean that we cannot update a collection of items conditionally in DynamoDB?

No! We can do it, it's just a bit more complicated. Here's what we need to do:

1. First, we need to fetch a collection of items that we'd like to update.
2. Then, run an update operation on each returned item.

If you were to write this operation on your own, it can quickly grow to over 100 lines of code if you'd like to handle pagination and all the cases gracefully.

Fortunately, that is also possible in Dynobase and it's much easier. For example:

Let's suppose we'd like to update every record in our database where ID has only two characters and prefix it with updated_ - an example ID 89 will become updated_89.

In SQL, you'd probably write something like this:

UPDATE my-table
SET id = CONCAT("updated_", id)
WHERE LEN(id) = 2
UPDATE my-table
SET id = CONCAT("updated_", id)
WHERE LEN(id) = 2

Let's recreate it in DynamoDB! First, you need to Download Dynobase.

Next, open it, select profile, region and table of your choice and click "Terminal", right next to the "Scan" / "Query" button, a small code editor will popup.

Inside this Editor, paste following piece of code:

function transform(data) {
  return Promise.all(
    data.map(async item => {
      const newItem =
        item.id.length === 2 ? { ...item, id: `updated_${item.id}` } : item
      await db.put({ Item: newItem, TableName }).promise()
      return newItem
    })
  )
}
function transform(data) {
  return Promise.all(
    data.map(async item => {
      const newItem =
        item.id.length === 2 ? { ...item, id: `updated_${item.id}` } : item
      await db.put({ Item: newItem, TableName }).promise()
      return newItem
    })
  )
}

and then run "Execute" - and voilà!

You dataset has been updated! Keep in mind that because Dynobase updates each record one-by-one under the hood, it can take quite long on larger datasets. You can also use the query filters to make sure that your update will only affect relevant piece of data.