UPDATE

DOCS • LANG

---

The UPDATE statement.

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

Basic Update

// (a): SQL syntax
const result = await client.query(
    `UPDATE TABLE public.users
    SET name = 'Jane', email = 'jane@example.com'`
);

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

The WHERE Clause

Find by simple expression:

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

// (b): Object-based syntax
const result = await client.database('public').table('users').update(
    { name: 'Jane', email: 'jane@example.com' },
    { 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: 'jane@example.com' },
    { 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 = 'jane@example.com'
    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: 'jane@example.com' },
    { where: [
        { isNotNull: 'role' },
        { isNotNull: { fn: ['COALESCE', 'email', 'phone'] } }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('users').update(
    { name: 'Jane', email: 'jane@example.com' },
    { 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 = 'jane@example.com'
    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: 'jane@example.com' },
    { 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: 'jane@example.com' },
    { 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

See also ➞ Magic Paths

Basic paths:

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