在SQLite数据库上更新一对多关系表的有效方法

Question

我有一个SQLite数据库，其中有两个表，具有如下一对多关系：

People
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`name` VARCHAR(200) NOT NULL,
`unique_identifier` VARCHAR(200)
Address
`id` VARCHAR(20) PRIMARY KEY AUTOINCREMENT,
`people_id` INTEGER,
`includes_unique_indentifier` VARCHAR(256),
`name` VARCHAR(200) NOT NULL,
FOREIGN KEY(people_id) REFERENCES People(id)

Address表中有一些记录的people_id为null。对于这些地址，可以通过匹配People.unique_identifier和Address.includes_unique_identifier的正则表达式来识别它们属于哪个人。

忽略数据冗余，我想更新每个Address.name以匹配其相应的People.name记录。

目前我的查询如下：

UPDATE address
SET name = (
SELECT notes
FROM people
WHERE address.people_id = id OR address.includes_unique_indentifier LIKE ("%" || unique_identifier || "%")
)

据我所知，这是一个效率低下的查询，因为它会遍历Address表，并为表中的每个记录递归地运行子查询。我正在寻找一种有效执行此更新的方法。我使用的SQLite版本不支持UPDATE查询中的FROM子句。

目前，Address表上不存在索引。

Answer 1

如果您计划升级到支持UPDATE ... FROM ...语法(3.30.0+(的版本，您可以使用此查询来模拟表的联接：

UPDATE address AS a
SET name = p.name
FROM people AS p
WHERE p.id = a.people_id OR a.includes_unique_indentifier LIKE '%' || p.unique_identifier || '%';