CODEWITHSUNDEEP
mysqlnode.jsknex.js
I am L
I am L

Reputation: 4662

How to add multiple rows using "Insert ... ON DUPLICATE KEY UPDATE" using knex

so I've been playing around with knex lately, however I found myself on a situation where I don't know what to do anymore.

so I have this query:

knex.raw("INSERT INTO tablename (`col1`, `col2`, `col3`) VALUES (?, ?, ?) 
ON DUPLICATE KEY UPDATE col2 = VALUES(`col2`)", 
[
    ['val1', 'hello', 'world'],
    ['val2', 'ohayo', 'minasan'],
]);

And for some reasons It throws me an error Expected 2 bindings, saw 3.

I tried making it: 

knex.raw("INSERT INTO tablename (`col1`, `col2`, `col3`) VALUES (?, ?, ?) 
ON DUPLICATE KEY UPDATE col2 = VALUES(`col2`)", 
    ['val1', 'hello', 'world'],
    ['val2', 'ohayo', 'minasan'],
);

No error this time, but it only inserts the first array.

I also tried making the values an object:

[
    {col1: 'val1', col2: 'hello', col3: 'world'},
    {col1: 'val2', col2: 'ohayo', col3: 'minasan'},
]

But still no luck.

Upvotes: 7

Views: 11588

Answers (5)

I am L
I am L

Reputation: 4662

2022 Update

I now use onConflict for this one. This will work for PostgreSQL, MySQL, and SQLite databases

Let's say I have a table with Primary key or unique Key called key and value

How to use it:

await knex('workspaces_settings')
  .insert({
    key,
    value,
    created_at: new Date(),
    updated_at: new Date(),
  })
  .onConflict(['key', 'value'])
  .merge();

This will make it so if the key-value combination exist, it would simply update the values based on the field inside the insert function.

For multiple values

You simply just need to pass an array in the insert instead:


await knex('workspaces_settings')
  .insert([
    {
      key: '1',
      value: '1',
      created_at: new Date(),
      updated_at: new Date(),
    },
    {
      key: '2',
      value: '2',
      created_at: new Date(),
      updated_at: new Date(),
    },
  ])
  .onConflict(['key', 'value'])
  .merge();

Upvotes: 1

Konstantin Tarkus
Konstantin Tarkus

Reputation: 38428

const db = knex({/* config */});

const records = [
  { id: "1", col1: "hello", col2: "world"],
  { id: "2", col1: "ohayo", col2: "minasan"]
];

const { sql, bindings } = db.table("table").insert(records).toSQL();

// MySQL
// --------------
await db.raw(
  ` ${sql} ON DUPLICATE KEY UPDATE
    col1 = VALUES(col1),
    col2 = VALUES(col2)`,
  bindings
);

// PostgreSQL
// --------------
await db.raw(
  ` ${sql} ON CONFLICT ("id") DO UPDATE
    SET col1 = EXCLUDED.col1,
        col2 = EXCLUDED.col2`,
  bindings
);

Upvotes: 0

Ilarion Halushka
Ilarion Halushka

Reputation: 2343

The Nathan's solution won't work if you are using PostgreSQL, as there is no ON DUPLICATE KEY UPDATE. So in PostgreSQL you should use ON CONFLICT ("id") DO UPDATE SET : 

const insertOrUpdate = (knex, tableName, data) => {
  const firstData = data[0] ? data[0] : data;

  return knex().raw(
    knex(tableName).insert(data).toQuery() + ' ON CONFLICT ("id") DO UPDATE SET ' +
      Object.keys(firstData).map((field) => `${field}=EXCLUDED.${field}`).join(', ')
  );
};

If you are using Objection.js (knex's wrapper) then (and don't forget to import knex in this case): 

const insertOrUpdate = (model, tableName, data) => {
  const firstData = data[0] ? data[0] : data;

  return model.knex().raw(
    knex(tableName).insert(data).toQuery() + ' ON CONFLICT ("id") DO UPDATE SET ' +
      Object.keys(firstData).map((field) => `${field}=EXCLUDED.${field}`).join(', ')
  );
};

Upvotes: 3

Nathan Phillips
Nathan Phillips

Reputation: 12485

I wrote this code to insert/update either a single row as an object or multiple rows as an array of objects:

function insertOrUpdate(knex: Knex, tableName: string, data: any) {
  const firstData = data[0] ? data[0] : data;
  return knex.raw(knex(tableName).insert(data).toQuery() + " ON DUPLICATE KEY UPDATE " +
    Object.getOwnPropertyNames(firstData).map((field) => `${field}=VALUES(${field})`).join(", "));
}

Upvotes: 11

skiilaa
skiilaa

Reputation: 1282

If you only need to insert a fix number rows at a time, you could try this:

knex.raw("INSERT INTO tablename (`col1`, `col2`, `col3`) VALUES (?, ?, ?), (?, ?, ?) ON DUPLICATE KEY UPDATE col2 = VALUES(`col2`)", 
    ['val1', 'hello', 'world', 'val2', 'ohayo', 'minasan'],
);

If you don't know how many you need to insert at a time, it is possible to write a script that adds (?, ?, ?), as many times as needed.

var questionMarks = "";
var values = [];
var rows = [
    {col1: 'val1', col2: 'hello', col3: 'world'},
    {col1: 'val2', col2: 'ohayo', col3: 'minasan'},
];
rows.forEach(function(value, index){
    questionMarks += "("
    Object.keys(value).forEach(function(x){
         questionMarks += "?, ";
         values.push(value[x]);
    });
    questionMarks = questionMarks.substr(0, questionMarks.length - 2);
    questionMarks += "), ";
});
questionMarks = questionMarks.substr(0, questionMarks.length - 2); //cut off last unneeded comma and space
knex.raw("INSERT INTO tablename (`col1`, `col2`, `col3`) VALUES " + questionMarks + " ON DUPLICATE KEY UPDATE col2 = VALUES(`col2`)", values);

Upvotes: 6

Related Questions