Is it possible to update an array at specific index in existing row in ClickHouse db? Something like alter table mytable set arr[3]=8
-
how often updates like this are occurred? Is it part of data business-logic or just single fix? Is it applied to full table, why not defined where-clause?vladimir– vladimir2020-08-06 22:59:10 +00:00Commented Aug 6, 2020 at 22:59
-
@vladimir, this will be part of business-logic (will happen pretty frequently) and of course there will be a where clauseStrelnikov Lev– Strelnikov Lev2020-08-09 05:41:22 +00:00Commented Aug 9, 2020 at 5:41
Add a comment
|
1 Answer
create table xxx(A Int64, Person Nested (Name String, value String))
Engine=MergeTree order by A;
insert into xxx values (1, ['a','b','c'], ['aaa','bbb','ccc'])
if array index = 3 then name = '1'
alter table xxx update "Person.Name" =
arrayMap( i-> if(i=3,'1',"Person.Name"[i]), arrayEnumerate("Person.Name")) where 1;
select * from xxx;
┌─A─┬─Person.Name───┬─Person.value────────┐
│ 1 │ ['a','b','1'] │ ['aaa','bbb','ccc'] │
└───┴───────────────┴─────────────────────┘
if name = a then name = 1
alter table xxx update "Person.Name" =
arrayMap( i-> if(i='a','1',i), "Person.Name") where 1;
select * from xxx;
┌─A─┬─Person.Name───┬─Person.value────────┐
│ 1 │ ['1','b','c'] │ ['aaa','bbb','ccc'] │
└───┴───────────────┴─────────────────────┘
if name = c then value = 333
alter table xxx update "Person.value" =
arrayMap( (i,j) -> if(j='c','333', i), "Person.value", "Person.Name") where 1;
select * from xxx
┌─A─┬─Person.Name───┬─Person.value────────┐
│ 1 │ ['1','b','c'] │ ['aaa','bbb','333'] │
└───┴───────────────┴─────────────────────┘
