FROM (INSERT, REMOVE, or SET) command in Amazon QLDB - Amazon Quantum Ledger Database (Amazon QLDB)
SyntaxParametersNested collectionsReturn valueExamplesRunning programmatically using the driver

FROM (INSERT, REMOVE, or SET) 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, a statement that starts with FROM is a PartiQL extension that lets you to insert and remove specific elements within a document. You can also use this statement to update existing elements in a document, similar to the UPDATE command.

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

FROM-INSERT

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

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

FROM-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.

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

FROM-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 traditional UPDATE syntax.

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

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.

In this clause, you can also include one or more collections that are nested within the specified table. For more details, see Nested collections.

AS table_alias

(Optional) A user-defined alias that ranges over a table to be modified. All table aliases that are used in the SET, REMOVE, INSERT INTO, or WHERE clause must be declared in the FROM clause. 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 FROM 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.

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.

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.

Nested collections

While you can run a DML statement on a single table only, you can specify nested collections within documents in that table as additional sources. Each alias that you declare for a nested collection can be used in the WHERE clause and the SET, INSERT INTO, or REMOVE clause.

For example, the FROM sources of the following statement include both the VehicleRegistration table and the nested Owners.SecondaryOwners structure.

FROM VehicleRegistration r, @r.Owners.SecondaryOwners o
WHERE r.VIN = '1N4AL11D75C109151' AND o.PersonId = 'abc123'
SET o.PersonId = 'def456'

This example updates the specific element of the SecondaryOwners list that has a PersonId of 'abc123' within the VehicleRegistration document that has a VIN of '1N4AL11D75C109151'. This expression lets you specify an element of a list by its value rather than its index.

Return value

documentId – The unique ID of each document that you updated or deleted.

Examples

Modify an element within a document. If the element doesn't exist, it's inserted.

FROM Vehicle AS v
WHERE v.VIN = '1N4AL11D75C109151' AND v.Color = 'Silver'
SET v.Color = 'Shiny Gray'

Modify or insert an element and filter on the system-assigned document id metadata field.

FROM Vehicle AS v BY v_id
WHERE v_id = 'documentId'
SET v.Color = 'Shiny Gray'

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

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

Remove an existing element within a document.

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

Remove a whole document from a table.

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

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

FROM VehicleRegistration AS r
WHERE r.VIN = '1N4AL11D75C109151'
REMOVE r.Owners.SecondaryOwners[0]

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

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

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.

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

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

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

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

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

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: