# PartiQL statements for DynamoDB
Amazon DynamoDB supports the following PartiQL statements.
**Note**
DynamoDB does not support all PartiQL statements.
This reference provides basic syntax and usage examples of PartiQL statements that you manually run using the AWS CLI or APIs.
*Data manipulation language* (DML) is the set of PartiQL statements that you use to manage data in DynamoDB tables. You use DML statements to add, modify, or delete data in a table.
The following DML and query language statements are supported:
+ [PartiQL select statements for DynamoDB](ql-reference.select.md)
+ [PartiQL update statements for DynamoDB](ql-reference.update.md)
+ [PartiQL insert statements for DynamoDB](ql-reference.insert.md)
+ [PartiQL delete statements for DynamoDB](ql-reference.delete.md)
[Performing transactions with PartiQL for DynamoDB](ql-reference.multiplestatements.transactions.md) and [Running batch operations with PartiQL for DynamoDB](ql-reference.multiplestatements.batching.md) are also supported by PartiQL for DynamoDB.
# PartiQL select statements for DynamoDB
Use the `SELECT` statement to retrieve data from a table in Amazon DynamoDB.
Using the `SELECT` statement can result in a full table scan if an equality or IN condition with a partition key is not provided in the WHERE clause. A scan operation examines every item for the requested values and can use up the provisioned throughput for a large table or index in a single operation.
If you want to avoid full table scan in PartiQL, you can:
+ Author your `SELECT` statements to not result in full table scans by making sure your [WHERE clause condition](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html#ql-reference.select.parameters) is configured accordingly.
+ Disable full table scans using the IAM policy specified at [Example: Allow select statements and deny full table scan statements in PartiQL for DynamoDB](ql-iam.md#access-policy-ql-iam-example6), in the DynamoDB developer guide.
For more information see [Best practices for querying and scanning data](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-query-scan.html), in the DynamoDB developer guide.
**Topics**
+ [Syntax](#ql-reference.select.syntax)
+ [Parameters](#ql-reference.select.parameters)
+ [Examples](#ql-reference.select.examples)
## Syntax
```
SELECT expression [, ...]
FROM table[.index]
[ WHERE condition ] [ [ORDER BY key [DESC|ASC] , ...]
```
## Parameters
***expression***
(Required) A projection formed from the `*` wildcard or a projection list of one or more attribute names or document paths from the result set. An expression can consist of calls to [Use PartiQL functions with DynamoDB](ql-functions.md) or fields that are modified by [PartiQL arithmetic, comparison, and logical operators for DynamoDB](ql-operators.md).
***table***
(Required) The table name to query.
***index***
(Optional) The name of the index to query.
You must add double quotation marks to the table name and index name when querying an index.
```
SELECT *
FROM "TableName"."IndexName"
```
***condition***
(Optional) The selection criteria for the query.
To ensure that a `SELECT` statement does not result in a full table scan, the `WHERE` clause condition must specify a partition key. Use the equality or IN operator.
For example, if you have an `Orders` table with an `OrderID` partition key and other non-key attributes, including an `Address`, the following statements would not result in a full table scan:
```
SELECT *
FROM "Orders"
WHERE OrderID = 100
SELECT *
FROM "Orders"
WHERE OrderID = 100 and Address='some address'
SELECT *
FROM "Orders"
WHERE OrderID = 100 or OrderID = 200
SELECT *
FROM "Orders"
WHERE OrderID IN [100, 300, 234]
```
The following `SELECT` statements, however, will result in a full table scan:
```
SELECT *
FROM "Orders"
WHERE OrderID > 1
SELECT *
FROM "Orders"
WHERE Address='some address'
SELECT *
FROM "Orders"
WHERE OrderID = 100 OR Address='some address'
```
***key***
(Optional) A hash key or a sort key to use to order returned results. The default order is ascending (`ASC`) specify `DESC` if you want the results retuned in descending order.
**Note**
If you omit the `WHERE` clause, then all of the items in the table are retrieved.
## Examples
The following query returns one item, if one exists, from the `Orders` table by specifying the partition key, `OrderID`, and using the equality operator.
```
SELECT OrderID, Total
FROM "Orders"
WHERE OrderID = 1
```
The following query returns all items in the `Orders` table that have a specific partition key, `OrderID`, values using the OR operator.
```
SELECT OrderID, Total
FROM "Orders"
WHERE OrderID = 1 OR OrderID = 2
```
The following query returns all items in the `Orders` table that have a specific partition key, `OrderID`, values using the IN operator. The returned results are in descending order, based on the `OrderID` key attribute value.
```
SELECT OrderID, Total
FROM "Orders"
WHERE OrderID IN [1, 2, 3] ORDER BY OrderID DESC
```
The following query shows a full table scan that returns all items from the `Orders` table that have a `Total` greater than 500, where `Total` is a non-key attribute.
```
SELECT OrderID, Total
FROM "Orders"
WHERE Total > 500
```
The following query shows a full table scan that returns all items from the `Orders` table within a specific `Total` order range, using the IN operator and a non-key attribute `Total`.
```
SELECT OrderID, Total
FROM "Orders"
WHERE Total IN [500, 600]
```
The following query shows a full table scan that returns all items from the `Orders` table within a specific `Total` order range, using the BETWEEN operator and a non-key attribute `Total`.
```
SELECT OrderID, Total
FROM "Orders"
WHERE Total BETWEEN 500 AND 600
```
The following query returns the first date a firestick device was used to watch by specifying the partition key `CustomerID` and sort key `MovieID` in the WHERE clause condition and using document paths in the SELECT clause.
```
SELECT Devices.FireStick.DateWatched[0]
FROM WatchList
WHERE CustomerID= 'C1' AND MovieID= 'M1'
```
The following query shows a full table scan that returns the list of items where a firestick device was first used after 12/24/19 using document paths in the WHERE clause condition.
```
SELECT Devices
FROM WatchList
WHERE Devices.FireStick.DateWatched[0] >= '12/24/19'
```
# PartiQL update statements for DynamoDB
Use the `UPDATE` statement to modify the value of one or more attributes within an item in an Amazon DynamoDB table.
**Note**
You can only update one item at a time; you cannot issue a single DynamoDB PartiQL statement that updates multiple items. For information on updating multiple items, see [Performing transactions with PartiQL for DynamoDB](ql-reference.multiplestatements.transactions.md) or [Running batch operations with PartiQL for DynamoDB](ql-reference.multiplestatements.batching.md).
**Topics**
+ [Syntax](#ql-reference.update.syntax)
+ [Parameters](#ql-reference.update.parameters)
+ [Return value](#ql-reference.update.return)
+ [Examples](#ql-reference.update.examples)
## Syntax
```
UPDATE table
[SET | REMOVE] path [= data] […]
WHERE condition [RETURNING returnvalues]
::= [ALL OLD | MODIFIED OLD | ALL NEW | MODIFIED NEW] *
```
## Parameters
***table***
(Required) The table containing the data to be modified.
***path***
(Required) An attribute name or document path to be created or modified.
***data***
(Required) An attribute value or the result of an operation.
The supported operations to use with SET:
+ LIST\$1APPEND: adds a value to a list type.
+ SET\$1ADD: adds a value to a number or string set.
+ SET\$1DELETE: removes a value from a number or string set.
***condition***
(Required) The selection criteria for the item to be modified. This condition must resolve to a single primary key value.
***returnvalues***
(Optional) Use `returnvalues` if you want to get the item attributes as they appear before or after they are updated. The valid values are:
+ `ALL OLD *`- Returns all of the attributes of the item, as they appeared before the update operation.
+ `MODIFIED OLD *`- Returns only the updated attributes, as they appeared before the update operation.
+ `ALL NEW *`- Returns all of the attributes of the item, as they appear after the update operation.
+ `MODIFIED NEW *`- Returns only the updated attributes, as they appear after the `UpdateItem` operation.
## Return value
This statement does not return a value unless `returnvalues` parameter is specified.
**Note**
If the WHERE clause of the UPDATE statement does not evaluate to true for any item in the DynamoDB table, `ConditionalCheckFailedException` is returned.
## Examples
Update an attribute value in an existing item. If the attribute does not exist, it is created.
The following query updates an item in the `"Music"` table by adding an attribute of type number (`AwardsWon`) and an attribute of type map (`AwardDetail`).
```
UPDATE "Music"
SET AwardsWon=1
SET AwardDetail={'Grammys':[2020, 2018]}
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
```
You can add `RETURNING ALL OLD *` to return the attributes as they appeared before the `Update` operation.
```
UPDATE "Music"
SET AwardsWon=1
SET AwardDetail={'Grammys':[2020, 2018]}
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
RETURNING ALL OLD *
```
This returns the following:
```
{
"Items": [
{
"Artist": {
"S": "Acme Band"
},
"SongTitle": {
"S": "PartiQL Rocks"
}
}
]
}
```
You can add `RETURNING ALL NEW *` to return the attributes as they appeared after the `Update` operation.
```
UPDATE "Music"
SET AwardsWon=1
SET AwardDetail={'Grammys':[2020, 2018]}
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
RETURNING ALL NEW *
```
This returns the following:
```
{
"Items": [
{
"AwardDetail": {
"M": {
"Grammys": {
"L": [
{
"N": "2020"
},
{
"N": "2018"
}
]
}
}
},
"AwardsWon": {
"N": "1"
}
}
]
}
```
The following query updates an item in the `"Music"` table by appending to a list `AwardDetail.Grammys`.
```
UPDATE "Music"
SET AwardDetail.Grammys =list_append(AwardDetail.Grammys,[2016])
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
```
The following query updates an item in the `"Music"` table by removing from a list `AwardDetail.Grammys`.
```
UPDATE "Music"
REMOVE AwardDetail.Grammys[2]
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
```
The following query updates an item in the `"Music"` table by adding `BillBoard` to the map `AwardDetail`.
```
UPDATE "Music"
SET AwardDetail.BillBoard=[2020]
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
```
The following query updates an item in the `"Music"` table by adding the string set attribute `BandMembers`.
```
UPDATE "Music"
SET BandMembers =<<'member1', 'member2'>>
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
```
The following query updates an item in the `"Music"` table by adding `newbandmember` to the string set `BandMembers`.
```
UPDATE "Music"
SET BandMembers =set_add(BandMembers, <<'newbandmember'>>)
WHERE Artist='Acme Band' AND SongTitle='PartiQL Rocks'
```
# PartiQL delete statements for DynamoDB
Use the `DELETE` statement to delete an existing item from your Amazon DynamoDB table.
**Note**
You can only delete one item at a time. You cannot issue a single DynamoDB PartiQL statement that deletes multiple items. For information on deleting multiple items, see [Performing transactions with PartiQL for DynamoDB](ql-reference.multiplestatements.transactions.md) or [Running batch operations with PartiQL for DynamoDB](ql-reference.multiplestatements.batching.md).
**Topics**
+ [Syntax](#ql-reference.delete.syntax)
+ [Parameters](#ql-reference.delete.parameters)
+ [Return value](#ql-reference.delete.return)
+ [Examples](#ql-reference.delete.examples)
## Syntax
```
DELETE FROM table
WHERE condition [RETURNING returnvalues]
::= ALL OLD *
```
## Parameters
***table***
(Required) The DynamoDB table containing the item to be deleted.
***condition***
(Required) The selection criteria for the item to be deleted; this condition must resolve to a single primary key value.
***returnvalues***
(Optional) Use `returnvalues` if you want to get the item attributes as they appeared before they were deleted. The valid values are:
+ `ALL OLD *`- The content of the old item is returned.
## Return value
This statement does not return a value unless `returnvalues` parameter is specified.
**Note**
If the DynamoDB table does not have any item with the same primary key as that of the item for which the DELETE is issued, SUCCESS is returned with 0 items deleted. If the table has an item with same primary key, but the condition in the WHERE clause of the DELETE statement evaluates to false, `ConditionalCheckFailedException` is returned.
## Examples
The following query deletes an item in the `"Music"` table.
```
DELETE FROM "Music" WHERE "Artist" = 'Acme Band' AND "SongTitle" = 'PartiQL Rocks'
```
You can add the parameter `RETURNING ALL OLD *` to return the data that was deleted.
```
DELETE FROM "Music" WHERE "Artist" = 'Acme Band' AND "SongTitle" = 'PartiQL Rocks' RETURNING ALL OLD *
```
The `Delete` statement now returns the following:
```
{
"Items": [
{
"Artist": {
"S": "Acme Band"
},
"SongTitle": {
"S": "PartiQL Rocks"
}
}
]
}
```
# PartiQL insert statements for DynamoDB
Use the `INSERT` statement to add an item to a table in Amazon DynamoDB.
**Note**
You can only insert one item at a time; you cannot issue a single DynamoDB PartiQL statement that inserts multiple items. For information on inserting multiple items, see [Performing transactions with PartiQL for DynamoDB](ql-reference.multiplestatements.transactions.md) or [Running batch operations with PartiQL for DynamoDB](ql-reference.multiplestatements.batching.md).
**Topics**
+ [Syntax](#ql-reference.insert.syntax)
+ [Parameters](#ql-reference.insert.parameters)
+ [Return value](#ql-reference.insert.return)
+ [Examples](#ql-reference.insert.examples)
## Syntax
Insert a single item.
```
INSERT INTO table VALUE item
```
## Parameters
***table***
(Required) The table where you want to insert the data. The table must already exist.
***item***
(Required) A valid DynamoDB item represented as a [PartiQL tuple](https://partiql.org/docs.html). You must specify only *one* item and each attribute name in the item is case-sensitive and can be denoted with *single* quotation marks (`'...'`) in PartiQL.
String values are also denoted with *single* quotation marks (`'...'`) in PartiQL.
## Return value
This statement does not return any values.
**Note**
If the DynamoDB table already has an item with the same primary key as the primary key of the item being inserted, `DuplicateItemException` is returned.
## Examples
```
INSERT INTO "Music" value {'Artist' : 'Acme Band','SongTitle' : 'PartiQL Rocks'}
```