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]'`
);

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

The WHERE Clause

Find by simple expression:

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

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

// (c): Function-based syntax
const result = await client.database('public').table('users').update(
    { name: 'Jane', email: '[email protected]' },
    { where: [
        (q) => q.eq('name', (r) => r.value('John')),
        (q) => q.eq('role', (r) => r.value('guest'))
    ] }
);

Find by simple expression:

// (a): SQL syntax
const result = await client.query(
    `UPDATE TABLE public.users
    SET name = 'Jane', email = '[email protected]'
    WHERE role IS NOT NULL AND COALESCE(email, phone) IS NOT NULL)`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').update(
    { name: 'Jane', email: '[email protected]' },
    { where: [
        { isNotNull: 'role' },
        { isNotNull: { fn: ['COALESCE', 'email', 'phone'] } }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('users').update(
    { name: 'Jane', email: '[email protected]' },
    { where: [
        { isNotNull: 'role' },
        { isNotNull: { fn: ['COALESCE', 'email', 'phone'] } }
    ] }
);

Find by complex expression:

// (a): SQL syntax
const result = await client.query(
    `UPDATE TABLE public.users
    SET name = 'Jane', email = '[email protected]'
    WHERE (role = $1 OR role = $2) AND (
        email IS NOT NULL OR (
            phone IS NOT NULL AND country_code IS NOT NULL
        )
    )`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').update(
    { name: 'Jane', email: '[email protected]' },
    { where: [
        { some: [
            { eq: ['role', { binding: 'admin' }] },
            { eq: ['role', { binding: 'contributor' }] }
        ] },
        { some: [
            { isNotNull: 'email' },
            { every: [
                { isNotNull: 'phone' },
                { isNotNull: 'country_code' }
            ] }
        ] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('users').update(
    { name: 'Jane', email: '[email protected]' },
    { where: [
        (q) => q.some(
            (r) => r.eq('role', (s) => s.binding('admin')),
            (r) => r.eq('role', (s) => s.binding('contributor')),
        ),
        (q) => q.some(
            (r) => r.isNotNull('email'),
            (r) => r.every(
                (s) => s.isNotNull('phone'),
                (s) => s.isNotNull('country_code')
            )
        )
    ] }
);

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`,
);

// (c): Object-based syntax (2)
const result = await client.database('public').table('books').update(
    { 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`
);

// (c): Object-based syntax (2)
const result = await client.database('public').table('users').update(
    { 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

For now, this fails where no records were updated by the operation. Also, where more than one record is updated, only the first record has its dimensions processed. Put together, this means that this form of multi-dimensional update only works best where exactly one record is updated.

Both issues will be addressed soon.