---
title: "Updating and deleting rows"
description: "update, deleteFrom, allRows and ClickHouse mutations."
---

> Documentation Index
> Fetch the complete documentation index at: https://effect-orm.maple.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Updating and deleting rows

:::note
The examples on this page use Postgres until the ClickHouse versions are written. The query builder is the same in every dialect: import `@maple-dev/effect-orm/clickhouse` as `CH` instead, and use ClickHouse's column types and functions.
:::

`update(table)` and `deleteFrom(table)` build UPDATE and DELETE from the same table definitions,
like [`insertInto`](https://effect-orm.maple.dev/clickhouse/queries/inserts/). They are immutable values; `Database.run` compiles them for
its database's dialect and runs them.

```ts
import * as PG from "@maple-dev/effect-orm/postgres"

const Tickets = PG.table("tickets", {
	columns: { id: PG.int4, org: PG.text, seats: PG.int4, tags: PG.array(PG.text) },
	primaryKey: ["id"],
	tenantColumn: "org",
})

const bump = PG.update(Tickets)
	.set(($) => ({ seats: $.seats.add(1) }))
	.where(($) => [$.org.eq(PG.param.string("org")), $.seats.lt(5)])
	.returning("id", "seats")

const revoke = PG.deleteFrom(Tickets).where(($) => [$.id.eq(PG.param.int("id"))])

// yield* Db.run(bump, { org })   // [{ id, seats }]
// yield* Db.run(revoke, { id })  // []
```

## SET

`set` takes a record of values, params or expressions, or a callback that gets the row's
columns as `$`. A key left out (or `undefined`) keeps the existing value. Values are encoded
through the column's codec and bound on Postgres, as in an insert. A column that is not
writable (a ClickHouse `materialized` or `alias` column, see
[the row type](https://effect-orm.maple.dev/clickhouse/queries/inserts/#which-columns-have-defaults)) cannot be set. `UpdateSetOf<typeof T>`
names the record type.

`update(table)` offers only `set` until it has one (its type is `CHUpdateStart`).

A Postgres or SQLite column with `onUpdate` (an Effect, see
[client-side defaults](https://effect-orm.maple.dev/clickhouse/queries/inserts/#client-side-defaults)) is set by every update that does not
set it: `Database.run` runs the Effect once and adds the value to the SET. With a SET callback,
the Effect runs before the callback, and a value the callback gives wins. The update requires
what the Effect needs. `compile` refuses an update that does not set the column, unless it went
through `fillDefaults` first.

## WHERE, and writing every row

`where` works as in a query: a list of conditions, AND-joined, with an `undefined` one skipped,
so optional filters compose. A write with no `where` would change every row, so:

- an UPDATE or DELETE with no `where()` or `allRows()` is a type error, and compiling one
  that slipped past the types is a `QueryBuilderDefect`;
- calling `where` again ANDs the new conditions with the earlier ones;
- a `where()` whose conditions all came out `undefined` (or render to nothing) is a
  `QueryBuilderError`, because that happens with data (every optional filter absent) and would
  otherwise widen a filtered write to the whole table;
- `allRows()` says a write over every row is meant.

## RETURNING

On Postgres, `returning` works as on an insert: no arguments for every column, column names, or
a callback. `Database.run` returns the changed or deleted rows, decoded. Without it, a write
returns no rows.

## ClickHouse

ClickHouse has no `UPDATE ... SET` on every server, so `update` compiles to a mutation, and
`deleteFrom` to a lightweight delete. Both need a WHERE, so `allRows()` writes `WHERE 1`:

```sql
ALTER TABLE spans UPDATE Name = 'x'
WHERE OrgId = 'o' SETTINGS mutations_sync = 2

DELETE FROM spans
WHERE OrgId = 'o' SETTINGS lightweight_deletes_sync = 2
```

A mutation runs in the background: without `mutations_sync`, `Database.run` returns before the
rows change. Pass `settings({ mutations_sync: 2 })` when the caller reads its own write.
Mutations rewrite whole parts, so they suit occasional corrections, not per-request updates;
model frequently changing state with a `ReplacingMergeTree` and inserts instead. ClickHouse
cannot update a column of the sorting key. `returning` is refused on ClickHouse
(`QueryBuilderDefect`), and `settings` on Postgres.

## Tenant scope

An UPDATE or DELETE has the scope a query over the table with the same WHERE would have:
`"single-tenant"` when the WHERE pins the tenant column, `"cross-tenant"` otherwise (including
`allRows()`). An UPDATE that sets the tenant column to another value moves rows out of the
tenant, so it is `"cross-tenant"` too. A subquery in the SET or WHERE counts as it would in a
query: one that reads another tenant (or every tenant) makes the write `"cross-tenant"`, and a
write into a table without a tenant column takes the scope of what its subqueries read. The
same holds for a subquery in an insert's values or `onConflictDoUpdate`.

_(Backed by `src/ch/update.test.ts`, `src/database/database.test.ts` and
`tests/database.clickhouse.test.ts`.)_

Source: https://effect-orm.maple.dev/clickhouse/queries/updates-and-deletes/index.mdx