Deleting Items using PartiQL DynamoDB editor with IN clause

Question

I am trying to delete DynamoDB items using the PartiQL editor using the IN clause. A sample query below is erroring with message "ValidationException: IN operator must have a left hand argument of type Variable Reference and right hand argument of type Seq with at least one member".

Anyone tried to delete DynamoDB items using IN clause with partiql. I am trying to cleanup items in the table without wring the SDK code.

Query: DELETE FROM "<TABLE_NAME_HERE>" WHERE <HASH_KEY_HERE> IN (Select <HASH_KEY_HERE> from "<TABLE_NAME_HERE>")

Error: ValidationException: IN operator must have a left hand argument of type Variable Reference and right hand argument of type Seq with at least one member

Answer 1

As per the PartiQL docs, the WHERE condition must resolve to a single key, whereas you are trying multiple keys which is not supported:

condition

(Required) The selection criteria for the item to be deleted; this condition must resolve to a single primary key value.

While you can use BatchExecuteStatement to meet your needs, that is not possible on the PartiQL Web Editor.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.delete.html