How to fetch and update DynamoDB table items by PartiQL in aws?

Question

I have created a new column to an existing dynamo db table in aws. Now I want a one-time script to populate values to the newly created column for all existing records. I have tried with the cursor as shown below from the PartiQL editor in aws

DECLARE cursor CURSOR FOR SELECT CRMCustomerGuid FROM "Customer";

OPEN cursor;

WHILE NEXT cursor DO
  UPDATE "Customer"
  SET "TimeToLive" = 1671860761
  WHERE "CustomerGuid" = cursor.CRMCustomerGuid;
END WHILE

CLOSE cursor;

But I am getting the error message saying that ValidationException: Statement wasn't well formed, can't be processed: unexpected keyword

Any help is appreciated

Answer 1

From @hunterhacker's comment we know that cursors are not possible with PartiQL. Similarly, we are unable to run multiple types of executions in PartiQL's web editor thus we are unable to do a SELECT then UPDATE.

However, this is quite easily achieved using the CLI or SDK. Below is a simple bash script which will update all of the items in your table with a TTL value, execute from any linux/unix based shell:

for pk in `aws dynamodb scan --table-name Customer --projection-expression 'CustomerGuid' --query 'Items[*].pk.S' --output text`; do
    aws dynamodb update-item \
    --table-name Customer \
    --key '{"CustomerGuid": {"S": "'$pk'"}}' \
    --update-expression "SET #ttl = :ttl" \
    --expression-attribute-names '{"#ttl":"TimeToLive"}' \
    --expression-attribute-values '{":ttl":{"N": "1671860762"}}'
done

Answer 2

DynamoDB is not a relational database and PartiQL is not a full SQL implementation.

Here’s the docs on the language. Cursor isn’t in there.

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

My own advice would be to use the plain non-SQL interface first - because with it the calls you can make map directly to the things the database can do.

Once you understand that you may, in some contexts, leverage the PartiQL ability.