SQL UPDATE语句中如何程序化生成列名与对应WHERE条件

原生SQL不支持你示例中直接遍历映射表动态指定更新列的语法，可通过以下两种方案实现需求：

方案1：单次查询+行转列更新（性能最优） #

仅查询一次关联表，避免每个字段对应一个子查询带来的重复扫描开销，适合字段数量中等的场景，以你给出的具体场景为例：

UPDATE training_image ti
SET 
  left_hand_label = af_map.left_hand,
  right_hand_label = af_map.right_hand
  -- 其余字段按需追加
FROM (
  SELECT
    MAX(CASE WHEN annotation = 'left-hand' THEN heatmap_label END) AS left_hand,
    MAX(CASE WHEN annotation = 'right-hand' THEN heatmap_label END) AS right_hand
    -- 其余条件对应的列按需追加
  FROM annotated_frame
) af_map;

方案2：动态SQL（完全自动化） #

直接读取你的字段映射表自动生成UPDATE语句，无需手动书写每个字段的更新逻辑，适合字段数量极多、映射关系频繁调整的场景。

MySQL 示例 #

SET @sql = NULL;
-- 从映射表生成所有更新字段的逻辑
SELECT GROUP_CONCAT(
  CONCAT(column_name, ' = (SELECT heatmap_label FROM annotated_frame WHERE annotation = ''', condition_value, ''')')
  SEPARATOR ', '
) INTO @sql
FROM column_value_map;

-- 拼接完整更新语句并执行
SET @sql = CONCAT('UPDATE training_image SET ', @sql);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 示例 #

DO $$
DECLARE
  v_update_sql TEXT;
BEGIN
  -- 从映射表生成所有更新字段的逻辑
  SELECT 'UPDATE training_image SET ' || string_agg(
    quote_ident(column_name) || ' = (SELECT heatmap_label FROM annotated_frame WHERE annotation = ' || quote_literal(condition_value) || ')',
    ', '
  )
  INTO v_update_sql
  FROM column_value_map;
  
  -- 执行生成的更新语句
  EXECUTE v_update_sql;
END $$;

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