如何在INSERT SELECT语句中使用UPDATE

-- 创建临时表
CREATE TEMPORARY TABLE temp_table AS
SELECT id, new_value
FROM source_table
WHERE condition;

-- 更新目标表
UPDATE target_table
SET target_table.value = temp_table.new_value
FROM temp_table
WHERE target_table.id = temp_table.id;

-- 删除临时表（可选）
DROP TABLE temp_table;

UPDATE target_table
SET value = (SELECT new_value FROM source_table WHERE source_table.id = target_table.id)
WHERE EXISTS (SELECT 1 FROM source_table WHERE source_table.id = target_table.id);

-- 创建示例表
CREATE TABLE source_table (
    id INT PRIMARY KEY,
    old_value VARCHAR(100),
    new_value VARCHAR(100)
);

CREATE TABLE target_table (
    id INT PRIMARY KEY,
    value VARCHAR(100)
);

-- 插入示例数据
INSERT INTO source_table (id, old_value, new_value) VALUES (1, 'old1', 'new1'), (2, 'old2', 'new2');
INSERT INTO target_table (id, value) VALUES (1, 'old1'), (2, 'old2');

-- 使用临时表方法更新
CREATE TEMPORARY TABLE temp_table AS
SELECT id, new_value
FROM source_table;

UPDATE target_table
SET value = temp_table.new_value
FROM temp_table
WHERE target_table.id = temp_table.id;

-- 使用子查询方法更新
UPDATE target_table
SET value = (SELECT new_value FROM source_table WHERE source_table.id = target_table.id)
WHERE EXISTS (SELECT 1 FROM source_table WHERE source_table.id = target_table.id);

-- 查看更新后的结果
SELECT * FROM target_table;

BEGIN;

-- 执行更新操作

COMMIT;