如何使用SELECT查询返回的表名作为UPDATE操作的表名执行批量更新

需求实现方案 #

你需要用动态SQL实现，因为表名是动态生成的，无法直接写死在UPDATE语句中。下面给出主流数据库的具体实现方式：

1. 手动批量执行（所有数据库通用） #

先通过查询生成所有需要执行的UPDATE语句，核对无误后直接批量执行即可，这种方式最安全、易排查问题：

MySQL写法

SELECT CONCAT('UPDATE `', dataview_table, '` SET status = 5 WHERE id = ', id, ';') AS update_sql
FROM (
    SELECT DISTINCT -- 去重避免重复执行相同更新语句
       CONCAT('data_', REPLACE(ft.uid, '-', '_')) AS dataview_table,  
        ft.id
    FROM father_table ft
) t;

SQL Server写法

SELECT 'UPDATE [' + dataview_table + '] SET status = 5 WHERE id = ' + CAST(id AS VARCHAR(20)) + ';' AS update_sql
FROM (
    SELECT DISTINCT
       'data_' + REPLACE(ft.uid, '-', '_') AS dataview_table,  
        ft.id
    FROM father_table ft
) t;

PostgreSQL写法

SELECT format('UPDATE %I SET status = 5 WHERE id = %L;', dataview_table, id) AS update_sql
FROM (
    SELECT DISTINCT
       'data_' || REPLACE(ft.uid, '-', '_') AS dataview_table,  
        ft.id
    FROM father_table ft
) t;

2. 自动执行方案 #

如果需要一次性自动完成所有更新，可以用游标+动态SQL实现：

MySQL自动执行

DELIMITER //
CREATE PROCEDURE batch_update_status()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE target_table VARCHAR(255);
    DECLARE target_id INT;
    DECLARE cur CURSOR FOR 
        SELECT DISTINCT CONCAT('data_', REPLACE(ft.uid, '-', '_')) AS dataview_table, ft.id
        FROM father_table ft;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO target_table, target_id;
        IF done THEN LEAVE read_loop; END IF;
        SET @sql = CONCAT('UPDATE `', target_table, '` SET status = 5 WHERE id = ', target_id);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- 调用执行更新
CALL batch_update_status();

PostgreSQL自动执行

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT DISTINCT 'data_' || REPLACE(ft.uid, '-', '_') AS dataview_table, ft.id FROM father_table ft
    LOOP
        EXECUTE format('UPDATE %I SET status = 5 WHERE id = $1', rec.dataview_table) USING rec.id;
    END LOOP;
END $$;

注意事项 #

• 执行更新前务必先运行生成SQL的查询，核对语句格式、表名、条件完全符合预期后再执行，避免数据误改
• 子查询加DISTINCT是为了过滤你示例结果中重复的表名+id组合，避免重复执行相同的更新语句

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