ContextQMD
Back to Drizzle Orm

SQL Update

src/content/docs/update.mdx

latest6.1 KB
Original Source

import IsSupportedChipGroup from '@mdx/IsSupportedChipGroup.astro'; import Callout from '@mdx/Callout.astro'; import Section from '@mdx/Section.astro';

SQL Update

typescript
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:

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

Limit

<IsSupportedChipGroup chips={{ 'PostgreSQL': false, 'MySQL': true, 'SQLite': true, 'SingleStore': true, 'MSSQL': false, 'CockroachDB': false }} />

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

<Section> ```typescript await db.update(usersTable).set({ verified: true }).limit(2); ``` ```sql update "users" set "verified" = $1 limit $2; ``` </Section>

Order By

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

<Section> ```typescript 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));

```sql
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;
</Section>

Returning

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'SQLite': true, 'MySQL': false , 'SingleStore': false, 'MSSQL': false, 'CockroachDB': true }} /> You can update a row and get it back in PostgreSQL and SQLite:

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

Output

<IsSupportedChipGroup chips={{ 'MSSQL': true }} /> You can update a row and get back the row before updated and after:

typescript
type User = typeof users.$inferSelect;

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

To return partial users after update:

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

To return rows that were in database before update:

ts
type User = typeof users.$inferSelect;

const updatedUserId: { deleted: User }[] = await db.update(users)
  .set({ name: 'Mr. Dan' })
  .where(eq(users.name, 'Dan'))
  .output({ deleted: true });

To return both previous and new version on a row:

ts
type User = typeof users.$inferSelect;

const updatedUserId: { deleted: User, inserted: User }[] = await db.update(users)
  .set({ name: 'Mr. Dan' })
  .where(eq(users.name, 'Dan'))
  .output({ deleted: true, inserted: true });

with update clause

<Callout> Check how to use WITH statement with [select](/docs/select#with-clause), [insert](/docs/insert#with-insert-clause), [delete](/docs/delete#with-delete-clause) </Callout>

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

<Section> ```typescript copy 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 });

```sql
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"
</Section>

Update ... from

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': false, 'SQLite': true, 'SingleStore': false, 'MSSQL': false, 'CockroachDB': true }} />

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]

<Section> ```ts await db .update(users) .set({ cityId: cities.id }) .from(cities) .where(and(eq(cities.name, 'Seattle'), eq(users.name, 'John'))) ``` ```sql update "users" set "city_id" = "cities"."id" from "cities" where ("cities"."name" = $1 and "users"."name" = $2)

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

</Section>

You can also alias tables that are joined (in PG, you can also alias the updating table too).
<Section>
```ts
const c = alias(cities, 'c');
await db
  .update(users)
  .set({ cityId: c.id })
  .from(c);
sql
update "users" set "city_id" = "c"."id" 
from "cities" "c"
</Section>

<IsSupportedChipGroup chips={{ 'PostgreSQL': true, 'MySQL': false, 'SQLite': false, 'SingleStore': false, 'MSSQL': false, 'CockroachDB': true }} />

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

<Section> ```ts const updatedUsers = await db .update(users) .set({ cityId: cities.id }) .from(cities) .returning({ id: users.id, cityName: cities.name }); ``` ```sql update "users" set "city_id" = "cities"."id" from "cities" returning "users"."id", "cities"."name" ``` </Section>