UPDATE

DOCS • LANG

---

The UPDATE statement.

See APIS ➞ client.query(), table.update()

Section	Description
Basic Update	Run a basic UPDATE operation.
The SET Clause	-
The WHERE Clause	-
The RETURNING Clause	-
Multi-Dimensional Inserts	Insert multi-dimensional data structures without doing the rough work.

Basic Update

// (a): SQL syntax
const result = await client.query(
    `UPDATE TABLE public.users
    SET name = 'Jane',
        email = '[email protected]'
    WHERE id = 1`
);

// (b): Object-based syntax (1)
const result = await client.database('public').table('users').update({
    data: {
        name: 'Jane',
        email: '[email protected]'
    },
    where: [{ eq: ['id', 1] }]
});

// (c): Object-based syntax (2)
const result = await client.database('public').table('users').update({
    set: [
        ['name', 'Jane'],
        ['email', '[email protected]']
    ],
    where: [{ eq: ['id', 1] }]
});

The WHERE Clause

The RETURNING Clause

Multi-Dimensional Updates

While you could update relational data individually and manually create the relevant associations, Linked QL supports special path operators that let you express relationships graphically. (See ➞ Magic Paths.) Or if you want, you could simply pass in your raw multi-dimensional data and Linked QL will do a neat multi-dimensional update for you.

Example 1:

For each book entry updated, create or update a user, associated as author, with the specified email:

// (a): SQL syntax
const result = await client.query(
    `UPDATE public.books
    SET
        title = 'Beauty and the Beast',
        content = '(C) 2024 [email protected]\nBeauty and the Beast...',
        author ~> email = '[email protected]'
    WHERE id = 1`,
);

// (b): Object-based syntax (1)
const result = await client.database('public').table('books').update({
    data: {
        title: 'Beauty and the Beast',
        content: '(C) 2024 [email protected]\nBeauty and the Beast...',
        author: { email: '[email protected]' }
    },
    where: { eq: ['id', { value: 1 }] }
});

// (c): Object-based syntax (2)
const result = await client.database('public').table('books').update({
    set: [
        ['title', 'Beauty and the Beast'],
        ['content', '(C) 2024 [email protected]\nBeauty and the Beast...'],
        ['author', [
            ['email', '[email protected]']
        ]]
    ],
    where: { eq: ['id', { value: 1 }] }
});

Example 2:

For each user updated, create or update an associated book entry with the specified title and content, returning entire tree:

const result = await client.query(
    `UPDATE public.users
    SET name = 'John Doe 2025',
        author <~ public.books: (
            title,
            content
        ) = (
            'Beauty and the Beast',
            '(C) 2024 [email protected]\nBeauty and the Beast...'
        )
    WHERE email = '[email protected]'
    RETURNING id`
);

// (b): Object-based syntax (1)
const result = await client.database('public').table('users').update({
    data: {
        name: 'John Doe 2025',
        books: [{
            title: 'Beauty and the Beast',
            content: '(C) 2024 [email protected]\nBeauty and the Beast...'
        }]
    },
    where: { eq: ['email', { value: '[email protected]' }] }
});

Note

This syntax uses the second table name (books) as key with assumption that the implied table is located within the same schema (public) as the base table.

It also makes the assumption that there is only one foreign key in the second table (books) referencing the base table (users).

An error is thrown where any of these assumptions fails.

// (c): Object-based syntax (2)
const result = await client.database('public').table('users').update({
    set: [
        ['name', 'John Doe 2025'],
        ['books', [
            [
                ['title', 'Beauty and the Beast'],
                ['content', '(C) 2024 [email protected]\nBeauty and the Beast...']
            ]
        ]]
    ],
    where: { eq: ['email', { value: '[email protected]' }] }
});

Tip

This syntax lets you work at a more granular level. While the above, by default, makes the same assumptions as in the first syntax, you are able to:

1. explicitly specify the relevant foreign key using an explicit path expression, potentially supporting any number of references from second table to base table:

{
    set: [
        ['name', 'John Doe 2025'],
        [{ lpath: ['author', 'books'] }, [
            [
                ['title', 'Beauty and the Beast'],
                ['content', '(C) 2024 [email protected]\nBeauty and the Beast...']
            ]
        ]]
    ],
    explicitPaths: true,
    where: { eq: ['email', { value: '[email protected]' }] }
}

2. extend the previous to optionally qualify the second table name:

{
    set: [
        ['name', 'John Doe 2025'],
        [{ lpath: ['author', ['public','books']] }, [
            [
                ['title', 'Beauty and the Beast'],
                ['content', '(C) 2024 [email protected]\nBeauty and the Beast...']
            ]
        ]]
    ],
    explicitPaths: true,
    where: { eq: ['email', { value: '[email protected]' }] }
}