如何基于多paymentID的余额与价格批量修改activeStatus状态？

我有两张数据表：

• Table #1：offerID, activeStatus
• Table #2：offerID, paymentID, price

需求是：当某个paymentID对应的余额大于其关联的price时，修改Table1中的activeStatus值。目前我已经写出了针对固定多个paymentID的Node.js SQL代码，但不知道如何适配任意多paymentID的场景，现有代码如下：

update table1
set activeStatus = 0
where activeStatus = 1 and
(
    (paymentID = 1 and price > ${balance1}) or
    (paymentID = 2 and price > ${balance2}) or
    (paymentID = 3 and price > ${balance3}) or
    (paymentID = 4 and price > ${balance4}) or
    (paymentID = 5 and price > ${balance5}) or
    (paymentID = 6 and price > ${balance6})
)

update table1
set activeStatus = 1
where activeStatus = 0 and
(
    (paymentID = 1 and price <= ${balance1}) or
    (paymentID = 2 and price <= ${balance2}) or
    (paymentID = 3 and price <= ${balance3}) or
    (paymentID = 4 and price <= ${balance4}) or
    (paymentID = 5 and price <= ${balance5}) or
    (paymentID = 6 and price <= ${balance6})
)

方案1：使用参数化数组+JOIN子查询（推荐） #

这种方式无需编写大量OR条件，而是将paymentID和对应余额组成临时数据集，与Table2关联后批量更新Table1，性能更优，且天然支持任意数量的paymentID：

// 示例余额数据：可添加任意多paymentID和对应余额
const paymentBalances = [
  { paymentID: 1, balance: 100 },
  { paymentID: 2, balance: 200 },
  { paymentID: 3, balance: 150 }
];

// 生成参数数组和VALUES子句占位符
const values = paymentBalances.flatMap(item => [item.paymentID, item.balance]);
const placeholders = paymentBalances.map(() => '(?, ?)').join(',');

// 构建"余额不足时设置activeStatus为0"的SQL
const updateInactiveSql = `
UPDATE table1 t1
JOIN table2 t2 ON t1.offerID = t2.offerID
JOIN (VALUES ${placeholders}) AS pb(paymentID, balance) ON t2.paymentID = pb.paymentID
SET t1.activeStatus = 0
WHERE t1.activeStatus = 1 AND t2.price > pb.balance;
`;

// 构建"余额充足时设置activeStatus为1"的SQL
const updateActiveSql = `
UPDATE table1 t1
JOIN table2 t2 ON t1.offerID = t2.offerID
JOIN (VALUES ${placeholders}) AS pb(paymentID, balance) ON t2.paymentID = pb.paymentID
SET t1.activeStatus = 1
WHERE t1.activeStatus = 0 AND t2.price <= pb.balance;
`;

// 以mysql2库为例执行SQL（需提前配置数据库连接）
const mysql = require('mysql2/promise');
const connection = await mysql.createConnection({
  host: '你的主机',
  user: '用户名',
  password: '密码',
  database: '数据库名'
});

await connection.execute(updateInactiveSql, values);
await connection.execute(updateActiveSql, values);
await connection.end();

方案2：动态生成OR条件（兼容旧版数据库） #

如果你的数据库不支持VALUES子句，可以动态生成OR条件，但必须使用参数化查询避免SQL注入：

const paymentBalances = [
  { paymentID: 1, balance: 100 },
  { paymentID: 2, balance: 200 }
];

// 生成条件片段和参数数组
const inactiveConditions = [];
const activeConditions = [];
const inactiveParams = [];
const activeParams = [];

paymentBalances.forEach(item => {
  inactiveConditions.push('(t2.paymentID = ? AND t2.price > ?)');
  inactiveParams.push(item.paymentID, item.balance);
  
  activeConditions.push('(t2.paymentID = ? AND t2.price <= ?)');
  activeParams.push(item.paymentID, item.balance);
});

// 构建更新SQL
const updateInactiveSql = `
UPDATE table1 t1
JOIN table2 t2 ON t1.offerID = t2.offerID
SET t1.activeStatus = 0
WHERE t1.activeStatus = 1 AND (${inactiveConditions.join(' OR ')})
`;

const updateActiveSql = `
UPDATE table1 t1
JOIN table2 t2 ON t1.offerID = t2.offerID
SET t1.activeStatus = 1
WHERE t1.activeStatus = 0 AND (${activeConditions.join(' OR ')})
`;

// 执行SQL（以mysql2为例）
const mysql = require('mysql2/promise');
const connection = await mysql.createConnection({ /* 数据库配置 */ });
await connection.execute(updateInactiveSql, inactiveParams);
await connection.execute(updateActiveSql, activeParams);
await connection.end();

• 必须使用参数化查询，禁止直接拼接字符串，防止SQL注入风险。
• 如果paymentID数量极大（如上千条），建议分批次处理，避免SQL语句过长导致性能下降。
• 确保两张表的offerID关联关系正确，否则会出现错误更新。

内容的提问来源于stack exchange，提问作者Rustamom