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
    ...

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?

SimonOP•2/1/24, 3:41 PM

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•2/1/24, 11:33 PM

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

ON DUPLICATE KEY UPDATE with bulk insert?

Similar Threads

Similar Threads

Similar Threads