Drizzle Team•10mo ago

ON DUPLICATE KEY UPDATE with bulk insert?

INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
    AS new
ON DUPLICATE KEY UPDATE
    age = new.age
    ...

INSERT INTO beautiful (name, age)
    VALUES
    ('Helen', 24),
    ('Katrina', 21),
    ('Samia', 22),
    ('Hui Ling', 25),
    ('Yumie', 29)
    AS new
ON DUPLICATE KEY UPDATE
    age = new.age
    ...

any way to create such a query?

2 Replies

SimonOP•10mo ago

got it, can use something like this

  const insertResult = await db
    .insert(leaderboard)
    .values(allUsersWithData)
    .onDuplicateKeyUpdate({
      set: {
        invitesCount: sql`VALUES(invitesCount)`,
      },
    })

  const insertResult = await db
    .insert(leaderboard)
    .values(allUsersWithData)
    .onDuplicateKeyUpdate({
      set: {
        invitesCount: sql`VALUES(invitesCount)`,
      },
    })

Angelelz•10mo ago

Most dialects let you reference the new data by the table name, and reference the old data with the excluded keyword

Gaming

Programming

ON DUPLICATE KEY UPDATE with bulk insert?