v1.0
75%
Benchmarks
Extension
Studio
Studio Package
Drizzle Run
Our goodies!
EdgeDB
Turso
🚀 Drizzle is giving you 10% off Turso Scaler and Pro for 1 Year 🚀
Payload
Xata
Neon
Nuxt
Hydra
Deco.cx
Tembo
SQLite Cloud
SingleStore
Become a Gold Sponsor
Product by Drizzle Team
One Dollar Stats $1 per mo web analytics

SQL Update

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

The object that you pass to update should have keys that match column names in your database schema. Values of undefined are ignored in the object: to set a column to null, pass null. You can pass SQL as a value to be used in the update object, like this:

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

Limit

PostgreSQL
MySQL
SQLite
SingleStore

Use .limit() to add limit clause to the query - for example:

await db.update(usersTable).set({ verified: true }).limit(2);
update "users" set "verified" = $1 limit $2;

Order By

Use .orderBy() to add order by clause to the query, sorting the results by the specified fields:

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;

Update with returning

PostgreSQL
SQLite
MySQL
SingleStore

You can update a row and get it back in PostgreSQL and SQLite:

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

with update clause

Check how to use WITH statement with select, insert, delete

Using the with clause can help you simplify complex queries by splitting them into smaller subqueries called common table expressions (CTEs):

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
SingleStore

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 [email protected]

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
SingleStore

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"