DT
Drizzle Team•14mo ago
Simon

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
Simon
SimonOP•14mo 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
Angelelz•14mo ago
Most dialects let you reference the new data by the table name, and reference the old data with the excluded keyword

Did you find this page helpful?