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

Examples coming soon.

The RETURNING Clause

Examples coming soon.

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 the 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 the first syntax, you are able to:

1. use an explicit path expression, thus, potentially allowing for any number of references from the implied 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...']
            ]
        ]]
    ],
    where: { eq: ['email', { value: '[email protected]' }] }
}

2. extend the previous to optionally qualify the implied 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...']
            ]
        ]]
    ],
    where: { eq: ['email', { value: '[email protected]' }] }
}