Drizzle Team•10mo ago

Can someone explain how can I use .onConflictDoUpdate with array of values?

If where is conflict somewhere with some value in array how can I use values from that array as new value? In documentation it shows how to handle that then setting one value but not array of values

// for single value
await db.insert(users)
  .values({ id: 1, name: 'Dan' })
  .onConflictDoUpdate({ target: users.id, set: { name: 'John' } });

// for single value
await db.insert(users)
  .values({ id: 1, name: 'Dan' })
  .onConflictDoUpdate({ target: users.id, set: { name: 'John' } });

but how about this

await db.insert(users)
  .values([{ id: 1, name: 'Dan' }, { id: 2, name: 'Tom' }, { id: 3, name: 'Martin' }])
  .onConflictDoUpdate({ target: users.id, set: ??? }); // if where was conflict on id 2 how can I set new name using values in array

await db.insert(users)
  .values([{ id: 1, name: 'Dan' }, { id: 2, name: 'Tom' }, { id: 3, name: 'Martin' }])
  .onConflictDoUpdate({ target: users.id, set: ??? }); // if where was conflict on id 2 how can I set new name using values in array

2 Replies

IcemourneOP•10mo ago

incase someone has same problem I fixed like this

await db.insert(users)
  .values([{ id: 1, name: 'Dan' }, { id: 2, name: 'Tom' }, { id: 3, name: 'Martin' }])
  .onConflictDoUpdate({
    target: users.id,
    set: {
      name: sql`excluded.name`
    } 
  });

await db.insert(users)
  .values([{ id: 1, name: 'Dan' }, { id: 2, name: 'Tom' }, { id: 3, name: 'Martin' }])
  .onConflictDoUpdate({
    target: users.id,
    set: {
      name: sql`excluded.name`
    } 
  });

benjick•7mo ago

excluded = the current iteration?

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table

TIL, thank you!

Gaming

Programming

Can someone explain how can I use .onConflictDoUpdate with array of values?