How can I retract and update values in an array in h2?
Since I'm trying to manage a Set
of distinct values in an array I don't know the ordinality/position of values in the array and can
therefore only operate by value and not use the position of values in the array - if possible?
(I can live with duplicate values in the array since I load queried array values into Set
s that will automatically filter out duplicate values.)
I can add a value with concatenation:
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]
// Concatenate
UPDATE A SET ints = ints || 3;
SELECT ints FROM A WHERE id = 1;
// [1, 2, 3]
But how can I retract a specific value?
// How to retract 2?
UPDATE A SET ints = ???;
SELECT ints FROM A WHERE id = 1;
// [1, 3]
// Or, how to retract 1 and 2?
UPDATE A SET ints = ???;
SELECT ints FROM A WHERE id = 1;
// [3]
And how can I update one value to another?
// How to replace 1 with 10?
UPDATE A SET ints = ???;
SELECT ints FROM A WHERE id = 1;
// [10, 2, 3]
// Or, how to replace 1 with 10 and 3 with 30?
UPDATE A SET ints = ???;
SELECT ints FROM A WHERE id = 1;
// [10, 2, 30]
I have tried variations on unnesting the values and filter them to exclude (retract) certain values, then aggregate them back along the following (wrong) lines without success:
UPDATE A
SET ints = ARRAY_AGG(
(SELECT * FROM UNNEST(SELECT ints FROM A) WHERE c1 != 2)
)
WHERE id = 1;
UPDATE: I can get the desired filtered values:
SELECT * FROM UNNEST(SELECT ints FROM A) where c1 != 2;
// C1
// 1
// 3
But then I can't create an array from those values (that I can set ints
to):
VALUES ARRAY(
SELECT * FROM UNNEST(SELECT ints FROM A) where c1 != 2
);
// C1
// [null, null]
How to retract 2?
UPDATE A
SET ints = ARRAY(
SELECT V
FROM TABLE(V INT = (SELECT ints FROM A WHERE id = 1))
WHERE V != 2
);
SELECT ints FROM A WHERE id = 1;
// [1, 3]
How to replace 1 with 10?
UPDATE A SET ints = ARRAY[1, 2, 3];
SELECT ints FROM A WHERE id = 1;
UPDATE A
SET ints = ARRAY(
SELECT CASE WHEN(V = 1, 10, V)
FROM TABLE(V INT = (SELECT ints FROM A WHERE id = 1))
);
SELECT ints FROM A WHERE id = 1;
// [10, 2, 3]