Support array element assignments in UPDATE statements

[lukaseder]

This works in PostgreSQL as intuitively expected:

create table t (i int array);
insert into t values (array[1, 2]);
update t set i[2] = 3;

H2 rejects the statement with this error:

SQL Error [42001] [42001]: Syntax error in SQL statement "update t set i[*][2] = 3"; expected "., ="; SQL statement: update t set i[2] = 3 [42001-212]

I think this would be a useful addition, given that array element reference is already available?
https://www.h2database.com/html/grammar.html?highlight=arrayElementReference&search=array#array_element_reference

I can't find anything specific about this in the SQL standard ISO/IEC 9075-2:2016(E) 9.2 Store assignment or 14.15 <set clause list>, so I'm not sure if it is specified like that...

[lukaseder]

Note, it seems that this syntax is available also in HSQLDB

[katzyn]

In the SQL Standard this syntax is specified in <update target>.

[lukaseder]

Right, thanks. I kinda missed it from the phrasing "If the i-th <set clause> contains an <update target> that immediately contains a <simple value specification>"