Implement batch updates (WAS: re-opening batch update discussion)

Feature discussion / request

1. Explain what is your use case We have ways of bulk inserting and deleting, but not for updating. I know it's a more rare use case but it's quite a glaring omission for an essential library like knex. Previous discussions on the topic wre marked out of scope which I don't agree with. https://github.com/knex/knex/issues/1271

2. Explain what kind of feature would support this

a new function exactly like batchInsert but for updates knex.batchUpdate

1. Give some API proposal, how the feature should work

We would use the CASE statement. https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-CASE https://dev.mysql.com/doc/refman/8.0/en/case.html

I came across this discussion from the django community where the op does some benchmarks using CASE to update things and, unsurprisingly it is an order of magnitude faster than doing N update statements in a loop.

https://groups.google.com/g/django-developers/c/a5ADv59TkBQ

The code was added back in 2015. https://github.com/django/django/pull/3825

Here is a quick proof of concept using raw, but it would be better to be included in the library so other ORMs built on top could have acess to this feature

function generateBatchUpdateSQL(tableName, data, identifier = 'id') {
  const keys = Object.keys(data[0]).filter((key) => key !== identifier);
  const updates = keys.map((key) => 
    `${key} = CASE ${data.map((row) => `WHEN ${identifier} = ${row[identifier]} THEN '${row[key]}'`).join(' ')} END`
  ).join(', ');

  const condition = `${identifier} IN (${data.map((row) => row[identifier]).join(', ')})`;

  return `UPDATE ${tableName} SET ${updates} WHERE ${condition}`;
}

// Usage
const tableName = 'my_table';
const data = [
  {id: 1, name: 'John', age: 25},
  {id: 2, name: 'Doe', age: 35}
];

const sql = generateBatchUpdateSQL(tableName, data);
await knex.raw(sql);