基于SQLite3，如何高效用表连接查询结果覆盖更新表t1？

嘿，刚好在SQLite里处理过类似的需求，给你梳理两种连接场景下最高效的实现方式，都是经过实际验证的：

这种场景下，我们要保留t1的每一行，同时把t2中匹配行的列值填充到t1新增的列里，不匹配的行新增列留为NULL。

步骤1：先给t1添加t2的目标列 #

首先得确保t1已经包含要从t2引入的列，根据t2列的类型调整语句：

ALTER TABLE t1 ADD COLUMN col_a TEXT; -- 替换为t2实际的列名和类型
ALTER TABLE t1 ADD COLUMN col_b INTEGER;

步骤2：高效更新匹配值（SQLite 3.33.0+推荐） #

从SQLite 3.33.0版本开始支持UPDATE FROM语法，这是目前最高效的方式，避免了子查询的重复计算：

UPDATE t1
SET col_a = t2.col_a, col_b = t2.col_b
FROM t2
WHERE t1.id = t2.id; -- 替换为你的实际连接键

旧版本兼容方案（SQLite <3.33.0） #

如果没法升级版本，只能用子查询实现，但效率会低一些，适合小数据量：

UPDATE t1
SET col_a = (SELECT col_a FROM t2 WHERE t2.id = t1.id),
    col_b = (SELECT col_b FROM t2 WHERE t2.id = t1.id);

这种场景下，t1最终只保留和t2匹配的行，同时填充t2的列值。这里有两种高效方案，根据数据量选择：

方案A：先删不匹配行，再更新（适合t1中不匹配行较少的情况） #

-- 第一步：删除t1中与t2无匹配的行
DELETE FROM t1
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.id = t2.id);

-- 第二步：更新匹配行的t2列值
UPDATE t1
SET col_a = t2.col_a, col_b = t2.col_b
FROM t2
WHERE t1.id = t2.id;

方案B：临时表替换法（适合t1中大部分行都不匹配的情况） #

通过临时表存储内连接结果，再替换原t1，减少多次IO操作：

-- 创建临时表存储内连接结果
CREATE TEMP TABLE temp_t1 AS
SELECT t1.*, t2.col_a, t2.col_b
FROM t1
JOIN t2 ON t1.id = t2.id; -- 替换为你的连接键

-- 清空原t1表
DELETE FROM t1;

-- 将临时表数据插入t1
INSERT INTO t1 SELECT * FROM temp_t1;

-- 清理临时表（可选，会话结束会自动删除）
DROP TABLE temp_t1;

• 给连接键加索引：这是提升大表连接效率的核心！确保t1和t2的连接键都有索引：

  CREATE INDEX IF NOT EXISTS idx_t1_id ON t1(id);
  CREATE INDEX IF NOT EXISTS idx_t2_id ON t2(id);
  

• 用事务包裹所有操作：避免中间出错导致数据不一致，同时大幅提升性能（减少磁盘IO次数）：

  BEGIN TRANSACTION;
  -- 这里放你的所有操作（添加列、更新/删除/插入）
  COMMIT;
  

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