SQLite中含子查询的group_concat结合INSERT INTO报错求助

错误原因 #

你用INSERT INTO SalesOrderNew (MenuCode)是尝试新增一行数据，但SalesOrderNew表的CustomerCode列带有NOT NULL约束，新增行时未给该列赋值，触发了约束校验错误。而你的实际需求是为现有行（MenuCode为NULL的行）填充计算后的MenuCode，应该使用UPDATE语句而非INSERT。

修正后的SQL语句 #

基础更新语句（单记录对应单个MenuCode） #

如果每个SalesOrderID对应唯一的MenuCode计算结果，直接用UPDATE结合子查询更新：

UPDATE SalesOrderNew
SET MenuCode = (
    SELECT 'C' || 
           CASE 
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 <= 6 THEN 1
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 <= 13 THEN 2
               ELSE 3
           END ||
           CASE 
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (1, 8, 15) THEN 1
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (2, 9, 16) THEN 2
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (3, 10, 17) THEN 3
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (4, 11, 18) THEN 4
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (5, 12, 19) THEN 5
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (6, 13, 20) THEN 6
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (7, 14, 21) THEN 7
           END ||
           MealCode ||
           (SELECT CustTypeCodeNew FROM CustomerNew WHERE CustCodeNew LIKE SalesOrderNew.CustomerCode)
    FROM SalesOrderNew sub
    WHERE sub.SalesOrderID = SalesOrderNew.SalesOrderID
)
WHERE MenuCode IS NULL;

支持多记录合并的更新（保留group_concat） #

如果单个SalesOrderID对应多条记录，需要用group_concat合并结果，可先预计算所有SalesOrderID对应的MenuCode，再关联更新：

WITH MenuCodeCalculations AS (
    SELECT 
        SalesOrderID,
        group_concat('C' || 
           CASE 
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 <= 6 THEN 1
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 <= 13 THEN 2
               ELSE 3
           END ||
           CASE 
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (1, 8, 15) THEN 1
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (2, 9, 16) THEN 2
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (3, 10, 17) THEN 3
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (4, 11, 18) THEN 4
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (5, 12, 19) THEN 5
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (6, 13, 20) THEN 6
               WHEN (CAST(julianday(mealdate) AS INT) - cast(julianday('2022-08-22') AS INT)) % 21 + 1 IN (7, 14, 21) THEN 7
           END ||
           MealCode ||
           (SELECT CustTypeCodeNew FROM CustomerNew WHERE CustCodeNew LIKE SalesOrderNew.CustomerCode)) AS MenuCode
    FROM SalesOrderNew
    GROUP BY SalesOrderID
)
UPDATE SalesOrderNew
SET MenuCode = mc.MenuCode
FROM MenuCodeCalculations mc
WHERE SalesOrderNew.SalesOrderID = mc.SalesOrderID
AND SalesOrderNew.MenuCode IS NULL;

额外注意事项 #

• 检查CustomerNew表中是否存在与SalesOrderNew.CustomerCode匹配的记录，避免子查询返回NULL导致MenuCode为空。如果需要处理无匹配的情况，可在子查询外添加COALESCE(..., 默认值)。
• 执行更新前建议先运行对应的SELECT语句验证计算结果是否符合预期，避免批量更新错误数据。

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