SQL Update

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

Update with returning

PostgreSQL

SQLite

MySQL

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"

Benchmarks

Studio

Become a Gold Sponsor