[FEATURE]: Built-in updateMany helper for multi-row, multi-condition CASE/WHEN updates (composite keys, chunking, typed arithmetic)

### Feature hasn't been suggested before.

- [x] I have verified this feature I'm about to request hasn't been suggested before.

### Describe the enhancement you want to request

# Built-in `updateMany` helper for multi-row, multi-condition CASE/WHEN updates (composite keys, chunking, typed arithmetic)

Drizzle supports bulk **insert** via `.values([...])`, but there's no built-in equivalent for bulk **update with per-row, per-column values**. The only documented approach (https://orm.drizzle.team/docs/guides/update-many-with-different-value) is to hand-write a `CASE WHEN ... THEN ... END` SQL fragment per column, which:

- has to be re-implemented per project (see #1794, Discussion #2518, Discussion #2557 — multiple independent reimplementations of the same pattern)
- has no support for **composite keys** (`WHERE (col1, col2) IN ((v1,v2), ...)`) — every existing community wrapper I've found (including the one in Discussion #2518) assumes a single `id` column
- has no built-in **chunking** for large row counts (Postgres has bind-parameter limits, and a single `UPDATE ... CASE` with thousands of rows is a real footgun)
- has no ergonomic way to express **relative updates** (`SET col = col + value`) inside the same per-row CASE structure — you end up hand-writing `sql\`${col} + ${value}\`` per branch
- silently produces wrong results if you're not careful about which key (raw SQL column name vs. schema property name) you use to build the `.set()` object — there's no compile-time signal when this goes wrong, it just drops the column from the update with no error

## Proposed API

A `updateMany()` helper, conceptually:

```ts
await updateMany({
db, // or tx
table: productItemsTable,
target: ["productItemId", "shopId"], // single column or composite array
rows: [
{ productItemId: "...", shopId: 1, qtyDelta: 5 },
{ productItemId: "...", shopId: 1, qtyDelta: -2 },
],
set: ({ current, row }) => ({
sellableStock: current.sellableStock.plus(row.qtyDelta), // SET col = col + value
updatedAt: sql`now()`,
}),
chunkSize: 500, // splits into multiple queries automatically, warns if no tx
});
```

Behavior:
- `target` accepts a single column or a tuple → builds `inArray(...)` for single-key, or `(col1, col2) IN ((v1,v2), ...)` for composite keys
- `set` is optional — when omitted, every non-index key present on each row is treated as a direct value to assign via CASE/WHEN
- `current..plus(value)` / `.minus(value)` produce `sql\`${col} + ${value}\`` / `sql\`${col} - ${value}\`` for atomic relative updates, fully typed against the column's inferred data type
- Rows exceeding `chunkSize` are split into sequential `UPDATE` statements; a runtime warning is emitted if `db` is not a transaction (since chunked updates aren't atomic without one)
- Throws on duplicate index keys within the same batch (silent last-write overwrite is a common bug source otherwise)

## Why this should live in drizzle-orm rather than user-land

This is the same argument as bulk insert: the CASE/WHEN SQL generation itself is mechanical and a natural candidate for `drizzle-orm` to own, but every team writing this from scratch (#1794, Discussion #2518, Discussion #2557 are all independent implementations of the same ~80% of the same logic) hits the same edge cases — composite keys, chunking, relative arithmetic, and key-shape footguns (schema property name vs. raw column name) — slightly differently and usually without test coverage for all of them.

A reference implementation (single column + composite key support, chunking, typed `current.col.plus/minus`, duplicate-key detection) is available below and can be contributed as a PR if there's interest in adding this to core or as an official `drizzle-orm/utils` style helper, rather than staying a per-project utility.

## Reference implementation

This is the `updateMany` helper we use in production (Postgres + drizzle-orm). It handles composite keys, chunking, and typed relative updates via `current.col.plus()/.minus()`. `Db`/`Tx` below are project-specific aliases for `NodePgDatabase` and its transaction type — substitute your own.

updateMany.ts (click to expand)

```ts
import { Column, getColumns, getTableName, inArray, SQL, sql } from "drizzle-orm";
import { PgTable, PgUpdateSetSource } from "drizzle-orm/pg-core";

// ─── Types
type Db = typeof db
type Tx = Parameters[0]>[0]
────────────────────────────────────────────────────────────────────

type ColNames = keyof T["_"]["columns"] & string;

type ColData> =
T["_"]["columns"][K]["_"]["data"];

type UpdateRow> =
{ [K in TIndex]-?: ColData } &
{ [K in Exclude, TIndex>]?: ColData };

/**
* Allows arbitrary helper fields (prefixed _ by convention) alongside DB columns.
* - null → sets DB column to NULL
* - undefined → skips column (sparse update)
* - key absent → skips column (sparse update)
*/
type UpdateRowWithExtras> =
UpdateRow & Record;

// ─── CurrentColumn ────────────────────────────────────────────────────────────

interface CurrentColumn {
readonly _sql: SQL;
/** SET col = col + value */
plus(value: TData extends string ? number : TData): SQL;
/** SET col = col - value */
minus(value: TData extends string ? number : TData): SQL;
}

type CurrentProxy = {
readonly [K in ColNames]: CurrentColumn>;
};

type SetValue = SQL | CurrentColumn;

type SetBuilder<
T extends PgTable,
TIndex extends ColNames,
TRow extends UpdateRowWithExtras,
> = (ctx: { current: CurrentProxy; row: TRow }) =>
Partial<{ [K in Exclude, TIndex>]: SetValue }>;

export type UpdateManyOptions<
T extends PgTable,
TIndex extends ColNames,
TRow extends UpdateRowWithExtras = UpdateRow,
> = {
db: Db | Tx;
table: T;
/**
* Single or composite key used to identify each row.
* @example "id"
* @example ["shopId", "productId"]
*/
target: TIndex | [TIndex, ...TIndex[]];
rows: TRow[];
/**
* Optional: override how SET values are computed per row.
* Omit for static value updates.
*
* @example Arithmetic
* set: ({ current, row }) => ({
* sellableStock: current.sellableStock.minus(row._qtyToDeduct),
* updatedAt: sql`now()`,
* })
*/
set?: SetBuilder;
/**
* Max rows per query. Splits large updates into multiple queries.
* ⚠️ Always pass a transaction as `db` when rows exceed this limit.
* @default 500
*/
chunkSize?: number;
};

// ─── Internal helpers ─────────────────────────────────────────────────────────

function isTx(db: Db | Tx): db is Tx {
// PgTransaction instances have a rollback method; Db (NodePgDatabase) does not
return "rollback" in db;
}

function chunkArray(arr: T[], size: number): T[][] {
const result: T[][] = [];
for (let i = 0; i < arr.length; i += size) {
result.push(arr.slice(i, i + size));
}
return result;
}

function makeCurrentProxy(
allColumns: Record,
tableName: string
): CurrentProxy {
return new Proxy({} as CurrentProxy, {
get(_, prop: string) {
const col = allColumns[prop];
if (!col) {
throw new Error(
`[updateMany] "${tableName}": column "${prop}" referenced in set() does not exist`
);
}
const colSql = sql`${col}`;
return {
_sql: colSql,
plus: (value: unknown) => sql`${colSql} + ${value}`,
minus: (value: unknown) => sql`${colSql} - ${value}`,
} satisfies CurrentColumn;
},
});
}

function isCurrentColumn(val: unknown): val is CurrentColumn {
return (
typeof val === "object" &&
val !== null &&
"_sql" in val &&
"plus" in val &&
"minus" in val
);
}

function resolveSetValue(val: SetValue): SQL {
return isCurrentColumn(val) ? val._sql : (val as SQL);
}

// ─── Core builders ────────────────────────────────────────────────────────────

function buildCaseClauses<
T extends PgTable,
TIndex extends ColNames,
TRow extends UpdateRowWithExtras,
>(
allColumns: Record,
indexKeys: TIndex[],
rows: TRow[],
current: CurrentProxy,
setBuilder?: SetBuilder
): Record {
const result: Record = {};

// Collect update keys from ALL rows — never just rows[0]
const updateKeys: string[] = setBuilder
? [...new Set(
rows.flatMap((row) =>
Object.keys(setBuilder({ current, row })).filter(
(k) => !(indexKeys as string[]).includes(k)
)
)
)]
: [...new Set(
rows.flatMap((row) =>
Object.keys(row).filter(
(k) => !(indexKeys as string[]).includes(k)
)
)
)];

for (const colKey of updateKeys) {
const col = allColumns[colKey];
// Helper field (e.g. _qtyToDeduct) — not a DB column, skip silently
if (!col) continue;

const chunks: SQL[] = [];

for (const row of rows) {
let setValue: SQL | undefined;

if (setBuilder) {
const built = setBuilder({ current, row });
const rawVal = built[colKey as keyof typeof built];
if (rawVal === undefined) continue;
setValue = resolveSetValue(rawVal as SetValue);
} else {
const rawVal = (row as Record)[colKey];
// undefined / key absent → sparse skip | null → SET col = NULL
if (!(colKey in row) || rawVal === undefined) continue;
setValue = sql`${rawVal}`;
}

const whenParts = indexKeys.map(
(idxKey) =>
sql`${allColumns[idxKey] as Column} = ${(row as Record)[idxKey]}`
);

const when =
whenParts.length === 1
? whenParts[0]!
: sql.join(whenParts, sql` and `);

chunks.push(sql`when ${when} then ${setValue}`);
}

if (chunks.length === 0) continue;

result[colKey] = sql`(case ${sql.join(chunks, sql` `)} end)`;
}

return result;
}

function buildWhereClause<
T extends PgTable,
TIndex extends ColNames,
>(
allColumns: Record,
indexKeys: TIndex[],
rows: UpdateRowWithExtras[]
): SQL {
if (rows.length === 0) {
throw new Error("[updateMany] empty batch passed to buildWhereClause");
}

if (indexKeys.length === 1) {
const idxCol = allColumns[indexKeys[0]!] as Column;
const ids = rows.map((r) => (r as Record)[indexKeys[0]!]);
return inArray(idxCol, ids);
}

// Composite: (col1, col2) IN ((v1, v2), ...)
const colRefs = sql.join(
indexKeys.map((k) => sql`${allColumns[k] as Column}`),
sql`, `
);
const tuples = sql.join(
rows.map((row) =>
sql`(${sql.join(
indexKeys.map((k) => sql`${(row as Record)[k]}`),
sql`, `
)})`
),
sql`, `
);

return sql`(${colRefs}) in (${tuples})`;
}

// ─── Public API ───────────────────────────────────────────────────────────────

export async function updateMany<
T extends PgTable,
TIndex extends ColNames,
TRow extends UpdateRowWithExtras = UpdateRow,
>({
db,
table,
target,
rows,
set,
chunkSize = 500,
}: UpdateManyOptions): Promise {
if (rows.length === 0) return;

const tableName = getTableName(table);
const allColumns = getColumns(table);
const indexKeys = (Array.isArray(target) ? target : [target]) as TIndex[];

// Validate index columns exist on the table
for (const key of indexKeys) {
if (!allColumns[key]) {
throw new Error(
`[updateMany] "${tableName}": index column "${key}" does not exist`
);
}
}

// Detect duplicate index keys — prevents silent partial overwrites
const seen = new Set();
for (const row of rows) {
const compositeKey = indexKeys
.map((k) => String((row as Record)[k]))
.join("::");
if (seen.has(compositeKey)) {
throw new Error(
`[updateMany] "${tableName}": duplicate index key { ${indexKeys
.map((k) => `${k}: ${(row as Record)[k]}`)
.join(", ")
} }`
);
}
seen.add(compositeKey);
}

// Warn when chunking without a transaction — partial update possible on failure
if (rows.length > chunkSize && !isTx(db)) {
console.warn(
`[updateMany] "${tableName}": ${rows.length} rows → ` +
`${Math.ceil(rows.length / chunkSize)} queries. ` +
`Pass a transaction to guarantee atomicity.`
);
}

const current = makeCurrentProxy(allColumns, tableName);
const batches = chunkArray(rows, chunkSize);

for (const batch of batches) {
const setClauses = buildCaseClauses(allColumns, indexKeys, batch, current, set);
if (Object.keys(setClauses).length === 0) continue;

const where = buildWhereClause(allColumns, indexKeys, batch);
await db
.update(table)
.set(setClauses as PgUpdateSetSource)
.where(where);
}
}
```

## Related

- #1794 — same underlying CASE/WHEN pattern, scoped to single-column `where` conditions; this request additionally covers composite keys, chunking, and relative (`+`/`-`) updates
- #4630 — different approach (`VALUES`-based bulk update via `UPDATE ... FROM (VALUES ...)`), worth considering as an alternative SQL strategy but doesn't by itself solve the composite-key / chunking / arithmetic ergonomics gap
- Discussion #2518, Discussion #2557 — community-authored CASE/WHEN wrappers, both single-column-key only

Start with the reference implementation named updateMany.ts and compare it with the existing update-many-with-different-value guide. Done means reaching an agreed core or official helper design covering per-row updates, composite keys, chunking, typed arithmetic, and duplicate-key detection, with the required validation and tests identified.

Written by the indexing model from the issue text.