SQLite中实现反向LIKE匹配的UPDATE查询问题

一、SQLite原生实现（优先推荐） #

要解决table.a（含带额外字符的species）与table.b（纯净species）的匹配更新，核心是利用SQLite的字符串匹配和关联查询，同时结合genus字段确保属级匹配，避免误更新：

基础版（单匹配场景） #

如果table.b中每个genus + species组合唯一，可直接用JOIN关联更新：

UPDATE table.a
SET inatcode = table.b.inatcode
FROM table.b
WHERE table.a.genus = table.b.genus
  AND table.a.species LIKE '%' || table.b.species || '%'
  AND table.a.inatcode IS NULL;

• 用||拼接通配符%，实现table.b.species是table.a.species子串的匹配
• 仅更新inatcode为空的行，避免覆盖手动添加的已有值
• 匹配genus字段，防止不同属的同名物种错误匹配

多匹配处理版 #

若table.b存在同一属内多个物种名可能匹配table.a同一条记录的情况，用子查询取第一个有效匹配：

UPDATE table.a
SET inatcode = (
  SELECT inatcode
  FROM table.b
  WHERE table.a.genus = table.b.genus
    AND table.a.species LIKE '%' || table.b.species || '%'
  LIMIT 1
)
WHERE table.a.inatcode IS NULL
  AND EXISTS (
    SELECT 1
    FROM table.b
    WHERE table.a.genus = table.b.genus
      AND table.a.species LIKE '%' || table.b.species || '%'
  );

• 子查询通过LIMIT 1确保只取第一个匹配的inatcode
• EXISTS子句过滤掉无匹配的行，避免将空值写入原有记录

二、R DBI包辅助处理 #

若SQLite原生逻辑无法满足复杂匹配需求，可通过R读取数据后处理再写回：

library(DBI)
library(dplyr)
library(stringr)

# 连接SQLite数据库
con <- dbConnect(RSQLite::SQLite(), "your_database_file.db")

# 读取两张表数据
df_a <- dbReadTable(con, "table.a")
df_b <- dbReadTable(con, "table.b")

# 匹配并更新inatcode字段
df_updated <- df_a %>%
  # 仅处理未手动赋值的行
  filter(is.na(inatcode)) %>%
  # 按属关联两张表
  left_join(df_b, by = "genus", suffix = c(".a", ".b")) %>%
  # 判断b的物种名是否是a的物种名的子串
  mutate(is_match = str_detect(species.a, species.b)) %>%
  filter(is_match) %>%
  # 同一属+物种组合仅保留第一个匹配结果
  group_by(genus, species.a) %>%
  slice(1) %>%
  # 整理字段，合并回原表
  select(genus, species = species.a, inatcode = inatcode.b) %>%
  right_join(df_a, by = c("genus", "species")) %>%
  # 优先用匹配到的编码，保留原有手动赋值
  mutate(inatcode = coalesce(inatcode.x, inatcode.y)) %>%
  select(genus, species, inatcode)

# 将更新后的数据写回数据库（覆盖原表，注意备份）
dbWriteTable(con, "table.a", df_updated, overwrite = TRUE)

# 关闭数据库连接
dbDisconnect(con)

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