Select your cookie preferences

We use essential cookies and similar tools that are necessary to provide our site and services. We use performance cookies to collect anonymous statistics, so we can understand how customers use our site and make improvements. Essential cookies cannot be deactivated, but you can choose “Customize” or “Decline” to decline performance cookies.

If you agree, AWS and approved third parties will also use cookies to provide useful site features, remember your preferences, and display relevant content, including relevant advertising. To accept or decline all non-essential cookies, choose “Accept” or “Decline.” To make more detailed choices, choose “Customize.”

Differences between a relational (SQL) database and DynamoDB when modifying data in a table

Focus mode
Differences between a relational (SQL) database and DynamoDB when modifying data in a table - Amazon DynamoDB

The SQL language provides the UPDATE statement for modifying data. Amazon DynamoDB uses the UpdateItem operation to accomplish similar tasks.

Modifying data in a table with SQL

In SQL, you would use the UPDATE statement to modify one or more rows. The SET clause specifies new values for one or more columns, and the WHERE clause determines which rows are modified. The following is an example.

UPDATE Music SET RecordLabel = 'Global Records' WHERE Artist = 'No One You Know' AND SongTitle = 'Call Me Today';

If no rows match the WHERE clause, the UPDATE statement has no effect.

Modifying data in a table in DynamoDB

In DynamoDB, you can use either the DynamoDB API or PartiQL (a SQL-compatible query language) to modify a single item. If you want to modify multiple items, you must use multiple operations.

DynamoDB API

With the DynamoDB API, you use the UpdateItem operation to modify a single item.

{ TableName: "Music", Key: { "Artist":"No One You Know", "SongTitle":"Call Me Today" }, UpdateExpression: "SET RecordLabel = :label", ExpressionAttributeValues: { ":label": "Global Records" } }

You must specify the Key attributes of the item to be modified and an UpdateExpression to specify attribute values. UpdateItem behaves like an "upsert" operation. The item is updated if it exists in the table, but if not, a new item is added (inserted).

UpdateItem supports conditional writes, where the operation succeeds only if a specific ConditionExpression evaluates to true. For example, the following UpdateItem operation does not perform the update unless the price of the song is greater than or equal to 2.00.

{ TableName: "Music", Key: { "Artist":"No One You Know", "SongTitle":"Call Me Today" }, UpdateExpression: "SET RecordLabel = :label", ConditionExpression: "Price >= :p", ExpressionAttributeValues: { ":label": "Global Records", ":p": 2.00 } }

UpdateItem also supports atomic counters, or attributes of type Number that can be incremented or decremented. Atomic counters are similar in many ways to sequence generators, identity columns, or autoincrement fields in SQL databases.

The following is an example of an UpdateItem operation to initialize a new attribute (Plays) to keep track of the number of times a song has been played.

{ TableName: "Music", Key: { "Artist":"No One You Know", "SongTitle":"Call Me Today" }, UpdateExpression: "SET Plays = :val", ExpressionAttributeValues: { ":val": 0 }, ReturnValues: "UPDATED_NEW" }

The ReturnValues parameter is set to UPDATED_NEW, which returns the new values of any attributes that were updated. In this case, it returns 0 (zero).

Whenever someone plays this song, we can use the following UpdateItem operation to increment Plays by one.

{ TableName: "Music", Key: { "Artist":"No One You Know", "SongTitle":"Call Me Today" }, UpdateExpression: "SET Plays = Plays + :incr", ExpressionAttributeValues: { ":incr": 1 }, ReturnValues: "UPDATED_NEW" }
PartiQL for DynamoDB

With PartiQL, you use the ExecuteStatement operation to modify an item in a table, using the PartiQL Update statement.

The primary key for this table consists of Artist and SongTitle. You must specify values for these attributes.

UPDATE Music SET RecordLabel ='Global Records' WHERE Artist='No One You Know' AND SongTitle='Call Me Today'

You can also modify multiple fields at once, such as in the following example.

UPDATE Music SET RecordLabel = 'Global Records' SET AwardsWon = 10 WHERE Artist ='No One You Know' AND SongTitle='Call Me Today'

Update also supports atomic counters, or attributes of type Number that can be incremented or decremented. Atomic counters are similar in many ways to sequence generators, identity columns, or autoincrement fields in SQL databases.

The following is an example of an Update statement to initialize a new attribute (Plays) to keep track of the number of times a song has been played.

UPDATE Music SET Plays = 0 WHERE Artist='No One You Know' AND SongTitle='Call Me Today'

Whenever someone plays this song, we can use the following Update statement to increment Plays by one.

UPDATE Music SET Plays = Plays + 1 WHERE Artist='No One You Know' AND SongTitle='Call Me Today'
Note

For code examples using Update and ExecuteStatement, see PartiQL update statements for DynamoDB.

With the DynamoDB API, you use the UpdateItem operation to modify a single item.

{ TableName: "Music", Key: { "Artist":"No One You Know", "SongTitle":"Call Me Today" }, UpdateExpression: "SET RecordLabel = :label", ExpressionAttributeValues: { ":label": "Global Records" } }

You must specify the Key attributes of the item to be modified and an UpdateExpression to specify attribute values. UpdateItem behaves like an "upsert" operation. The item is updated if it exists in the table, but if not, a new item is added (inserted).

UpdateItem supports conditional writes, where the operation succeeds only if a specific ConditionExpression evaluates to true. For example, the following UpdateItem operation does not perform the update unless the price of the song is greater than or equal to 2.00.

{ TableName: "Music", Key: { "Artist":"No One You Know", "SongTitle":"Call Me Today" }, UpdateExpression: "SET RecordLabel = :label", ConditionExpression: "Price >= :p", ExpressionAttributeValues: { ":label": "Global Records", ":p": 2.00 } }

UpdateItem also supports atomic counters, or attributes of type Number that can be incremented or decremented. Atomic counters are similar in many ways to sequence generators, identity columns, or autoincrement fields in SQL databases.

The following is an example of an UpdateItem operation to initialize a new attribute (Plays) to keep track of the number of times a song has been played.

{ TableName: "Music", Key: { "Artist":"No One You Know", "SongTitle":"Call Me Today" }, UpdateExpression: "SET Plays = :val", ExpressionAttributeValues: { ":val": 0 }, ReturnValues: "UPDATED_NEW" }

The ReturnValues parameter is set to UPDATED_NEW, which returns the new values of any attributes that were updated. In this case, it returns 0 (zero).

Whenever someone plays this song, we can use the following UpdateItem operation to increment Plays by one.

{ TableName: "Music", Key: { "Artist":"No One You Know", "SongTitle":"Call Me Today" }, UpdateExpression: "SET Plays = Plays + :incr", ExpressionAttributeValues: { ":incr": 1 }, ReturnValues: "UPDATED_NEW" }
PrivacySite termsCookie preferences
© 2025, Amazon Web Services, Inc. or its affiliates. All rights reserved.