SQL 更新

await db.update(users)
  .set({ name: 'Mr. Dan' })
  .where(eq(users.name, 'Dan'));

传递给 update 的对象应具有与数据库架构中的列名相匹配的键。对象中值为 undefined 的键会被忽略：要将列设置为 null，请传递 null。您可以将 SQL 作为值传递，以在更新对象中使用，如下所示：

await db.update(users)
  .set({ updatedAt: sql`NOW()` })
  .where(eq(users.name, 'Dan'));

限制

PostgreSQL

MySQL

SQLite

使用 .limit() 将 limit 子句添加到查询中 - 例如：

await db.update(usersTable).set({ verified: true }).limit(2);

update "users" set "verified" = $1 limit $2;

排序

使用 .orderBy() 向查询添加 order by 子句，按指定字段对结果进行排序：

import { asc, desc } from 'drizzle-orm';

await db.update(usersTable).set({ verified: true }).orderBy(usersTable.name);
await db.update(usersTable).set({ verified: true }).orderBy(desc(usersTable.name));

// order by multiple fields
await db.update(usersTable).set({ verified: true }).orderBy(usersTable.name, usersTable.name2);
await db.update(usersTable).set({ verified: true }).orderBy(asc(usersTable.name), desc(usersTable.name2));

update "users" set "verified" = $1 order by "name";
update "users" set "verified" = $1 order by "name" desc;

update "users" set "verified" = $1 order by "name", "name2";
update "users" set "verified" = $1 order by "name" asc, "name2" desc;

带返回值的更新

PostgreSQL

SQLite

MySQL

您可以在 PostgreSQL 和 SQLite 中更新行并返回它：

const updatedUserId: { updatedId: number }[] = await db.update(users)
  .set({ name: 'Mr. Dan' })
  .where(eq(users.name, 'Dan'))
  .returning({ updatedId: users.id });

`with update`子句

查看如何在 select、insert、delete 中使用 WITH 语句

使用 with 子句可以通过将复杂查询拆分为称为公共表表达式 (CTE) 的较小子查询来简化查询：

const averagePrice = db.$with('average_price').as(
        db.select({ value: sql`avg(${products.price})`.as('value') }).from(products)
);

const result = await db.with(averagePrice)
		.update(products)
		.set({
			cheap: true
		})
		.where(lt(products.price, sql`(select * from ${averagePrice})`))
		.returning({
			id: products.id
		});

with "average_price" as (select avg("price") as "value" from "products") 
update "products" set "cheap" = $1 
where "products"."price" < (select * from "average_price") 
returning "id"

Update … from

PostgreSQL

MySQL

SQLite

As the SQLite documentation mentions:

The UPDATE-FROM idea is an extension to SQL that allows an UPDATE statement to be driven by other tables in the database. The “target” table is the specific table that is being updated. With UPDATE-FROM you can join the target table against other tables in the database in order to help compute which rows need updating and what the new values should be on those rows

Similarly, the PostgreSQL documentation states:

A table expression allowing columns from other tables to appear in the WHERE condition and update expressions

Drizzle also supports this feature starting from version drizzle-orm@0.36.3

await db
  .update(users)
  .set({ cityId: cities.id })
  .from(cities)
  .where(and(eq(cities.name, 'Seattle'), eq(users.name, 'John')))

update "users" set "city_id" = "cities"."id" 
from "cities" 
where ("cities"."name" = $1 and "users"."name" = $2)

-- params: [ 'Seattle', 'John' ]

You can also alias tables that are joined (in PG, you can also alias the updating table too).

const c = alias(cities, 'c');
await db
  .update(users)
  .set({ cityId: c.id })
  .from(c);

update "users" set "city_id" = "c"."id" 
from "cities" "c"

PostgreSQL

MySQL

SQLite

In Postgres, you can also return columns from the joined tables.

const updatedUsers = await db
  .update(users)
  .set({ cityId: cities.id })
  .from(cities)
  .returning({ id: users.id, cityName: cities.name });

update "users" set "city_id" = "cities"."id" 
from "cities" 
returning "users"."id", "cities"."name"

SQL 更新

限制

排序

带返回值的更新

with update子句

Update … from

`with update`子句