SQL Update

All the values provided to .set()are parameterized automatically. For example, this query:

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

will be translated to:

update [users] set [name] = @par0 where [users].[name] = @par1; -- params: ['Mr. Dan', '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.

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

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

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'));

Output

This method allows you to return values from the rows affected by the query. MSSQL supports returning inserted (new row values) and deleted (old row values) values.

If no fields are specified, all inserted values will be returned by default.

// Update cars and return all new values
const updatedCars: Car[] = await db.update(cars)
  .set({ color: 'red' })
  .output()
  .where(eq(cars.color, 'green'));

// Update cars and return all new values
const updatedCars: Car[] = await db.update(cars)
  .set({ color: 'red' })
  .output({ inserted: true })
  .where(eq(cars.color, 'green'));

// Update cars and return all old values
const updatedCarsIds: { deleted: Car }[] = await db.update(cars)
  .set({ color: 'red' })
  .output({ deleted: true })
  .where(eq(cars.color, 'green'));

// Update cars and return partial old and new values
const beforeAndAfter: { deleted: { oldColor: string }, inserted: { newColor: string } }[] = await db.update(cars)
  .set({ color: 'red' })
  .output({
    deleted: { oldColor: cars.color },
    inserted: { newColor: cars.color }
  })
  .where(eq(cars.color, 'green'));

update [cars] set [color] = 'red' output INSERTED.[name], INSERTED.[color] where [cars].[color] = 'green';
update [cars] set [color] = 'red' output INSERTED.[name], INSERTED.[color] where [cars].[color] = 'green';
update [cars] set [color] = 'red' output DELETED.[name], DELETED.[color] where [cars].[color] = 'green';
update [cars] set [color] = 'red' output INSERTED.[color], DELETED.[color] where [cars].[color] = 'green';