UPDATE - Amazon Quantum Ledger Database (Amazon QLDB)

UPDATE

Use the UPDATE statement to modify the value of one or more elements within a document in Amazon QLDB. If an element does not exist, it is inserted.

Syntax

UPDATE table [ AS table_alias ] [ BY id_alias ] SET element = data [, element = data, ... ] [ WHERE condition ]

Parameters

table

The user table containing the data to be modified. DML statements are only supported in the default user view. Each statement can only run on a single table.

AS table_alias

(Optional) A user-defined alias that ranges over a table to be updated. The AS keyword is optional.

BY id_alias

(Optional) A user-defined alias that binds to the id metadata field of each document in the result set. The alias must be declared in the UPDATE clause using the BY keyword. This is useful when you want to filter on the document ID while querying the default user view. For more information, see Using the BY clause to query document ID.

element

A document element to be created or modified.

data

A new value for the element.

condition

The selection criteria for the documents to be modified.

Note

If you omit the WHERE clause, then all of the documents in the table are modified.

Return value

documentId – The ID of each document that you updated.

Examples

Update a field in a document. If the field does not exist, it is inserted.

UPDATE Person AS p SET p.LicenseNumber = 'HOLLOR123ZZ' WHERE p.GovId = '111-22-3333'

Filter on the system-assigned document id metadata field.

UPDATE Person AS p BY pid SET p.LicenseNumber = 'HOLLOR123ZZ' WHERE pid = 'documentId'

Overwrite an entire document.

UPDATE Person AS p SET p = { 'FirstName' : 'Rosemarie', 'LastName' : 'Holloway', 'DOB' : `1977-06-18T`, 'GovId' : '111-22-3333', 'GovIdType' : 'Driver License', 'Address' : '4637 Melrose Street, Ellensburg, WA, 98926' } WHERE p.GovId = '111-22-3333'

Modify the PersonId field of the first element in the Owners.SecondaryOwners list within a document.

UPDATE VehicleRegistration AS r SET r.Owners.SecondaryOwners[0].PersonId = 'abc123' WHERE r.VIN = '1N4AL11D75C109151'

Running programmatically using the driver

To learn how to programmatically run this statement using the QLDB driver, see the following tutorials in Getting started with the driver: