update multiple fields

Hi is there a other way to update multiple fields currently i do it like this

    const updatePollOption = (option: PollOptionsSchema) => {
      const { isNew, id, ...options } = option;
      return db
        .update(tables.pollOptions)
        .set({ ...options, pollId: pollId })
        .where(eq(tables.pollOptions.id, option.id));
    };

    const result = await Promise.all(
      pollOptions.map((option) => updatePollOption(option))
    );

    const updatePollOption = (option: PollOptionsSchema) => {
      const { isNew, id, ...options } = option;
      return db
        .update(tables.pollOptions)
        .set({ ...options, pollId: pollId })
        .where(eq(tables.pollOptions.id, option.id));
    };

    const result = await Promise.all(
      pollOptions.map((option) => updatePollOption(option))
    );

9 Replies

MAST•16mo ago

You can pass an array to the set if I remember it correctly.

🇨🇭 Marko Bolliger <cannap>OP•16mo ago

yes but i have diffet id´s

rphlmr ⚡•16mo ago

Maybe do that in a transaction if you are concerned about consistency (all or nothing). This code is 👍 until you have hundreds poll options. (Batch update)

Angelelz•16mo ago

How would you do that in SQL? I like Drizzle bacause it doesn't abstract away too much, very close to sql and type safe If you want to update different rows in SQL with different data, you need to write different queries, right?

🇨🇭 Marko Bolliger <cannap>OP•16mo ago

i dont know

Angelelz•16mo ago

@Raphaël M (@rphlmr) ⚡ is right then. As long as you're not updating a whole lot of rows. I would do it in a transaction just to be safe, remember that Promise.all is not great if some of your promises reject. Maybe use Promise.allSettle So you know what rejected and what worked

ivanfeliciano•16mo ago

Like so:

   db
    .insert(table)
    .values(array)
    .onConflictDoUpdate({
        target: table.id,
        set: {
            field: sql`excluded.field`,
        },
    })

   db
    .insert(table)
    .values(array)
    .onConflictDoUpdate({
        target: table.id,
        set: {
            field: sql`excluded.field`,
        },
    })

I believe you will have to explicitly put all the columns in the set: {}

Angelelz•16mo ago

This is a great way!

Andrii Sherman•16mo ago

We will also make an excluded object available in callback, so you'll have a native suppoort for it

Gaming

Programming

update multiple fields