SQLite单语句实现：检查值匹配后插入多行并返回目标值

需求 #

• 检查db表（仅包含adr、v两列）中，adr等于指定参数的行是否存在值V
• 若V存在且与传入参数V_Param相等（含两者均为NULL的情况），则插入一批指定行
• 返回查询到的旧值V

原尝试语句及错误 #

原SQL语句：

WITH 
old_value AS (
    SELECT v FROM DB WHERE adr = ?1
),
check AS (
    SELECT EXISTS(
        SELECT 1 FROM old_value 
        WHERE v = ?2 OR (v IS NULL AND ?2 IS NULL)
    ) AS check_passed
),
do_insert AS (
    SELECT 
        CASE 
            WHEN (SELECT check_passed FROM check) = 1 
            THEN (
                INSERT OR REPLACE INTO DB (adr, v)
                SELECT value1, value2
                FROM (VALUES ("a1","v1"),("a2","v2")) vals(value1, value2)
            )
        END
    WHERE (SELECT check_passed FROM check) = 1
)
SELECT v AS old_value FROM old_value;

执行报错：

sqlite> .read asba2.sql
Error: near line 1: in prepare, near "check": syntax error (1)

问题根源：

1. check是SQLite的关键字，不能用作CTE（公共表表达式）的名称
2. SQLite不允许在SELECT语句的CASE分支中嵌套INSERT这类数据修改操作

解决方案 #

利用SQLite 3.35.0及以上版本支持WITH子句中包含数据修改语句的特性，可以实现单语句完成需求：

WITH 
old_value AS (
    -- 先查询出目标adr对应的旧值v
    SELECT v FROM DB WHERE adr = ?1
),
check_result AS (
    -- 判断旧值是否符合匹配条件（含NULL相等的情况）
    SELECT EXISTS(
        SELECT 1 FROM old_value 
        WHERE v = ?2 OR (v IS NULL AND ?2 IS NULL)
    ) AS check_passed
),
do_insert AS (
    -- 仅当检查通过时执行批量插入
    INSERT OR REPLACE INTO DB (adr, v)
    SELECT value1, value2
    FROM (VALUES ('a1','v1'),('a2','v2')) vals(value1, value2)
    WHERE (SELECT check_passed FROM check_result) = 1
)
-- 最后返回查询到的旧值
SELECT v AS old_value FROM old_value;

关键说明 #

• 将原CTE名称check改为check_result，避免与SQL关键字冲突
• 把插入操作直接放在CTEdo_insert中，通过WHERE子句控制仅在检查通过时执行
• 最终的SELECT语句返回最初查询到的旧值v，满足需求
• 若不需要替换已有行，可将INSERT OR REPLACE改为普通INSERT

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