update multiple rows

How can I update values in multiple rows at once?

6 Replies

Just use an update statement with where in this case it will update all rows by a filter simple update will update all rows

fasmOP•2y 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..

Andrii Sherman•2y 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

fasmOP•2y ago

amazing, thank you so much!! Let me give this a go new

Andrii Sherman•2y ago

you just need to check that inputs array is >0 before building this query and everything should work well

fasmOP•2y ago

works perfectly, thank you!!

Gaming

Programming

update multiple rows