H2数据库中数组值的删除与更新操作方法咨询

需求说明 #

需要在H2数据库中对数组字段执行按值删除和按值更新操作，无需关注数组元素的位置/顺序；允许数组中存在重复值，查询后会通过Set自动去重。

已掌握数组值添加方法（拼接）：

DROP TABLE A IF EXISTS;
CREATE TABLE A(id INT PRIMARY KEY AUTO_INCREMENT, ints INT ARRAY);
INSERT INTO A(ints) VALUES (ARRAY[1, 2]);
SELECT ints FROM A WHERE id = 1;
-- 结果: [1, 2]

-- 拼接添加值
UPDATE A SET ints = ints || 3;
SELECT ints FROM A WHERE id = 1;
-- 结果: [1, 2, 3]

按值删除数组元素 #

删除单个指定值 #

要删除数组中的指定值（比如删除2），通过UNNEST拆解数组，过滤目标值后用ARRAY_AGG重新聚合为数组：

UPDATE A
SET ints = (
    SELECT ARRAY_AGG(c1)
    FROM UNNEST((SELECT ints FROM A WHERE id = 1)) AS t(c1)
    WHERE c1 != 2
)
WHERE id = 1;

SELECT ints FROM A WHERE id = 1;
-- 结果: [1, 3]

删除多个指定值 #

如果要删除多个值（比如删除1和2），用NOT IN过滤目标值：

UPDATE A
SET ints = (
    SELECT ARRAY_AGG(c1)
    FROM UNNEST((SELECT ints FROM A WHERE id = 1)) AS t(c1)
    WHERE c1 NOT IN (1, 2)
)
WHERE id = 1;

SELECT ints FROM A WHERE id = 1;
-- 结果: [3]

按值替换数组元素 #

替换单个值 #

要将指定值替换为新值（比如把1替换成10），在聚合时用CASE语句判断替换：

UPDATE A
SET ints = (
    SELECT ARRAY_AGG(CASE WHEN c1 = 1 THEN 10 ELSE c1 END)
    FROM UNNEST((SELECT ints FROM A WHERE id = 1)) AS t(c1)
)
WHERE id = 1;

SELECT ints FROM A WHERE id = 1;
-- 结果: [10, 2, 3]

替换多个值 #

如果要替换多个值（比如把1换成10，3换成30），扩展CASE语句的条件即可：

UPDATE A
SET ints = (
    SELECT ARRAY_AGG(
        CASE 
            WHEN c1 = 1 THEN 10
            WHEN c1 = 3 THEN 30
            ELSE c1 
        END
    )
    FROM UNNEST((SELECT ints FROM A WHERE id = 1)) AS t(c1)
)
WHERE id = 1;

SELECT ints FROM A WHERE id = 1;
-- 结果: [10, 2, 30]

关于之前尝试的修正 #

你之前的写法问题在于UNNEST的调用方式和聚合逻辑，正确做法是在子查询中完整拆解当前行的数组、完成过滤/替换后再聚合，确保上下文关联到要更新的目标行。

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