DT
Drizzle Teamfasm

update multiple rows

How can I update values in multiple rows at once?
AS
Andrii Sherman•339d ago
Just use an update statement with where in this case it will update all rows by a filter simple update will update all rows
F
fasm•339d ago
thanks for getting back to me. I want do this by row ID as an identifier, not sure how to use the where statement like that. 
type NewPosition = {
    id: number,
    position: number
}
export const updateLinkPositions = async (newPositions: NewPosition[]) => {
    const promises = newPositions.map(({ id, position }) => {
        return db.update(linksTable).set({ position }).where(eq(linksTable.id, id))
    })
    return Promise.all(promises)
}
type NewPosition = {
    id: number,
    position: number
}
export const updateLinkPositions = async (newPositions: NewPosition[]) => {
    const promises = newPositions.map(({ id, position }) => {
        return db.update(linksTable).set({ position }).where(eq(linksTable.id, id))
    })
    return Promise.all(promises)
}
this is what I currently have..
AS
Andrii Sherman•339d ago
if you want to update each row with a different value - then it's not possible in SQL and then not possible in drizzle actually I just searched a bit and it seems to be possible with sql let me compose a query for that and check 
UPDATE links
SET position = (case when id = 1 then 'position1'
                     when id = 2 then 'position2'
                     when id = 3 then 'position3'
                end)
WHERE id in (1, 2, 3)
UPDATE links
SET position = (case when id = 1 then 'position1'
                     when id = 2 then 'position2'
                     when id = 3 then 'position3'
                end)
WHERE id in (1, 2, 3)
This is how it would look like in sql this will help you to make only 1 query to database only case here - we don't have a support for case when in drizzle, but we have magical sql template that may help here 
db.update(linksTable).set({ position: sql`<here will go case-when statement>` }).where(inArray(linksTable.id, [1, 2, 3]))
db.update(linksTable).set({ position: sql`<here will go case-when statement>` }).where(inArray(linksTable.id, [1, 2, 3]))
so the only thing left is to properly build case-when statement done, let me share a small example for that 
const inputs: NewPosition[] = [
  {
    id: 1,
    position: 10,
  },
  {
    id: 2,
    position: 11,
  },
  {
    id: 3,
    position: 12,
  },
];

const sqlChunks: SQL[] = [];
const ids: number[] = []

sqlChunks.push(sql`(case`)
for (const input of inputs) {
    sqlChunks.push(sql`when id = ${input.id} then ${input.position}`)
    ids.push(input.id)
}
sqlChunks.push(sql`end)`)


const finalSql: SQL = sql.join(sqlChunks, sql.raw(" "));

const res = await db.update(linksTable).set({position: finalSql}).where(inArray(linksTable.id, ids));
const inputs: NewPosition[] = [
  {
    id: 1,
    position: 10,
  },
  {
    id: 2,
    position: 11,
  },
  {
    id: 3,
    position: 12,
  },
];

const sqlChunks: SQL[] = [];
const ids: number[] = []

sqlChunks.push(sql`(case`)
for (const input of inputs) {
    sqlChunks.push(sql`when id = ${input.id} then ${input.position}`)
    ids.push(input.id)
}
sqlChunks.push(sql`end)`)


const finalSql: SQL = sql.join(sqlChunks, sql.raw(" "));

const res = await db.update(linksTable).set({position: finalSql}).where(inArray(linksTable.id, ids));
some references https://orm.drizzle.team/docs/sql#sqljoin we have a full docs on advanced sql usage for cases, when drizzle doesn't have support for something This code looks a bit harder, but you will get 1 query to database instead of multiple basically we just built case-when part in safe manner and put it in update query to drizzle
F
fasm•339d ago
amazing, thank you so much!! Let me give this a go new
AS
Andrii Sherman•339d ago
you just need to check that inputs array is >0 before building this query and everything should work well
F
fasm•339d ago
works perfectly, thank you!!
Want results from more Discord servers?
Add your server
More Posts
Running `drizzle-kit introspect:pg` returns "client password must be a string"I'm attempting to move from objection.js+knex.js over to Drizzle and I'm running `drizzle-kit introsnoob help : transaction not acid``` return await this.drizzle.db.transaction(async (trx) => { const u = await trx .seExplicit inferred types```ts async loginWithGoogle(user: NonNullable<Request['user']>) { const existingUser = await thiMany-to-Many where in far tableI'm trying to `findMany()` Pokemon(s) by a type name ``` pokemons pokemonsToType Cannot call onConflictDoNothing() or on onConflctDoUpdate() on selectHello guys, this is probably right in front of me, but when I insert at the end of values I cannot fTop-level await is not available in the configured target environment ("chrome87", "edge88", "es2020Hey I just upgraded to the v^0.26.2 and I get the following error: ``` Top-level await is not avaican you Infer relations?im really loving how drizzle plays well with relations, but im wondering is there a way to get typesubRows.map is not a functionI am getting a subRows.map is not a function error when adding the "with" parameter to my relationalPostgres's Serial column type doesn't automatically have a defaultHello, when using postgres's `serial` types, and setting them as primary keys, there is an issue curHow to insert into a table without providing valuesI've got the following schema for users in sqlite, it only contains an id, which is an auto incremenDoent infer type in Callback syntax for queryIm querying the users table like this `db.query.user.findFirst({ where: (user, { eq }) => { Bug When Trying To Increment A Fieldthis set the field to 0 instead of increasing by one it used to work in other routes ``` await Maximum call stack exceeded with relation to selfI have the following table ``` export const category = pgTable('categories', { id: serial('id').importing into schema.ts fileHi, I am using turborepo and I have defined my schema in `packages/schema/user.ts` and in my main AWhat versions of MySQL are supported? I have JSON_ARRAYAGG issues with 5.7 and AWS Aurora ServerlessCan you confirm what versions of MySQL you intend on supporting? Relational queries with joins use J`$with` example in docs error "unsupported: with expression in select statement"I have a user with id of 42, just like the docs https://orm.drizzle.team/docs/crud#with-clause but Infer type for relational queryIs there anyway to infer the type of a relational queries result? For example if I have a user who in drizzle.config.ts: "Cannot use import statement outside a module"happens when I try to `push` ```ts import type { Config } from "drizzle-kit"; import { env } from "Error when using Drizzle (Non-abstract class 'PgSelect<TTableName, TSelection, TSelectMode, [...])I'm getting the following error when I try to use Drizzle: ```> graphql-server@1.0.0 start > npm runGenerate classes/interfaces from existing schemas?Messing around with drizzle, and I was wondering if there was any way to generate a class or interfa