使用knex在单个查询中更新多行数据MsSQL

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (2, 'Bob');
INSERT INTO users (id, name) VALUES (3, 'Charlie');

const knex = require('knex')({
  client: 'mssql',
  connection: {
    host: 'your_host',
    user: 'your_user',
    password: 'your_password',
    database: 'your_database'
  }
});

const updates = [
  { id: 1, name: 'Alice Updated' },
  { id: 2, name: 'Bob Updated' },
  { id: 3, name: 'Charlie Updated' }
];

const query = knex('users')
  .update({
    name: knex.raw(`CASE id ${updates.map(u => `WHEN ${u.id} THEN '${u.name}'`).join(' ')} END`)
  })
  .whereIn('id', updates.map(u => u.id))
  .toString();

console.log(query);

knex.raw(query)
  .then(() => {
    console.log('Update successful');
  })
  .catch(err => {
    console.error('Update failed', err);
  })
  .finally(() => {
    knex.destroy();
  });

const knex = require('knex')({
  client: 'mssql',
  connection: {
    host: 'your_host',
    user: 'your_user',
    password: 'your_password',
    database: 'your_database'
  }
});

const updates = [
  { id: 1, name: 'Alice Updated' },
  { id: 2, name: 'Bob Updated' },
  { id: 3, name: 'Charlie Updated' }
];

async function updateUsers() {
  try {
    // 创建临时表
    await knex.schema.raw(`
      CREATE TABLE #TempUsers (
        id INT,
        name VARCHAR(255)
      )
    `);

    // 插入更新数据到临时表
    await knex('#TempUsers').insert(updates);

    // 使用 JOIN 语句更新目标表
    await knex('users')
      .join('#TempUsers', 'users.id', '=', '#TempUsers.id')
      .update({
        'users.name': knex.ref('#TempUsers.name')
      });

    console.log('Update successful');
  } catch (err) {
    console.error('Update failed', err);
  } finally {
    // 删除临时表
    await knex.schema.raw('DROP TABLE #TempUsers');
    knex.destroy();
  }
}

updateUsers();