UPDATE command in Amazon QLDB

PDFRSS

Important

End of support notice: Existing customers will be able to use Amazon QLDB until end of support on 07/31/2025. For more details, see Migrate an Amazon QLDB Ledger to Amazon Aurora PostgreSQL.

In Amazon QLDB, use the UPDATE command to modify the value of one or more elements within a document. If an element doesn't exist, it's inserted.

You can also use this command to explicitly insert and remove specific elements within a document, similar to FROM (INSERT, REMOVE, or SET) statements.

Note

To learn how to control access to run this PartiQL command on specific tables, see Getting started with the standard permissions mode in Amazon QLDB.

Syntax

UPDATE-SET

Update one or more elements within a document. If an element doesn't exist, it's inserted. This is semantically the same as the FROM-SET statement.

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

UPDATE-INSERT

Insert a new element within an existing document. To insert a new top-level document into a table, you must use INSERT.

UPDATE table_name [ AS table_alias ] [ BY id_alias ]
INSERT INTO element VALUE data [ AT key_name ]
[ WHERE condition ]

UPDATE-REMOVE

Remove an existing element within a document, or remove an entire top-level document. The latter is semantically the same as the traditional DELETE syntax.

UPDATE table_name [ AS table_alias ] [ BY id_alias ]
REMOVE element
[ WHERE condition ]

Parameters

table_name

The name of 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.

AT key_name

A key name to be added within the documents to be modified. You must specify the corresponding VALUE along with the key name. This is required for inserting a new value AT a specific position within a document.

WHERE 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 unique ID of each document that you updated.

Examples

Update a field in a document. If the field doesn't exist, it's 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'

Insert {'Mileage':26500} as a top-level name-value pair within a document in the Vehicle table.

UPDATE Vehicle AS v
INSERT INTO v VALUE 26500 AT 'Mileage'
WHERE v.VIN = '1N4AL11D75C109151'

Append {'PersonId':'abc123'} as a name-value pair in the Owners.SecondaryOwners field of a document in the VehicleRegistration table. Note that Owners.SecondaryOwners must already exist and must be a list data type for this statement to be valid. Otherwise, the keyword AT is required in the INSERT INTO clause.

UPDATE VehicleRegistration AS r
INSERT INTO r.Owners.SecondaryOwners VALUE { 'PersonId' : 'abc123' }
WHERE r.VIN = '1N4AL11D75C109151'

Insert {'PersonId':'abc123'} as the first element in the existing Owners.SecondaryOwners list within a document.

UPDATE VehicleRegistration AS r
INSERT INTO r.Owners.SecondaryOwners VALUE {'PersonId' : 'abc123'} AT 0
WHERE r.VIN = '1N4AL11D75C109151'

Append multiple name-value pairs to the existing Owners.SecondaryOwners list within a document.

UPDATE VehicleRegistration AS r
INSERT INTO r.Owners.SecondaryOwners << {'PersonId' : 'abc123'}, {'PersonId' : 'def456'} >>
WHERE r.VIN = '1N4AL11D75C109151'

Remove an existing element within a document.

UPDATE Person AS p
REMOVE p.Address
WHERE p.GovId = '111-22-3333'

Remove a whole document from a table.

UPDATE Person AS p
REMOVE p
WHERE p.GovId = '111-22-3333'

Remove the first element of the Owners.SecondaryOwners list within a document in the VehicleRegistration table.

UPDATE VehicleRegistration AS r
REMOVE r.Owners.SecondaryOwners[0]
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:

• Java: Quick start tutorial | Cookbook reference

• .NET: Quick start tutorial | Cookbook reference

• Go: Quick start tutorial | Cookbook reference

• Node.js: Quick start tutorial | Cookbook reference

• Python: Quick start tutorial | Cookbook reference