UPDATE command in Amazon QLDB
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
[ AStable_alias
] [ BYid_alias
] SETelement
=data
[,element
=data
, ... ] [ WHEREcondition
]
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
[ AStable_alias
] [ BYid_alias
] INSERT INTOelement
VALUEdata
[ ATkey_name
] [ WHEREcondition
]
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
[ AStable_alias
] [ BYid_alias
] REMOVEelement
[ WHEREcondition
]
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 theUPDATE
clause using theBY
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 valueAT
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:
-
Node.js: Quick start tutorial | Cookbook reference
-
Python: Quick start tutorial | Cookbook reference