feat: support update...set..from

[rad-pat]

Search before asking

• [X] I had searched in the issues and found no similar issues.

Version

https://github.com/datafuselabs/databend/releases/tag/v1.2.423-nightly

What's Wrong?

Further to https://github.com/datafuselabs/databend/issues/9455 update statement does not support update from query. I tried 2 different ways to update using query, but both are not working. Please see details on how to reproduce

How to Reproduce?

create table source(c0 int, c1 string);
create table target(c0 int, c1 string);
insert into source values (1, 'one'), (2, 'two'), (3, 'three');
insert into target values (1, ''), (2, ''), (3, '');

-- attempt 1 from query

update target set c1 = s.c1 from (select c0, c1 from source) as s where target.c0 = s.c0;
error: APIError: ResponseError with 1005: error: 
  --> SQL:1:29
  |
1 | update target set c1 = s.c1 from (select c0, c1 from source) as s where target.c0 = s.c0
  |                             ^^^^ unexpected `from`, expecting `FORMAT`, `TRIM`, `SOME`, <CubeRoot>, <Factorial>, <ShiftRight>, `ColumnPosition`, `CURRENT_TIMESTAMP`, `OR`, `NOT`, `XOR`, `TRUE`, `RLIKE`, `COUNT`, `FALSE`, `REGEXP`, `SOUNDS`, `EXTRACT`, `TRY_CAST`, `INTERVAL`, `POSITION`, <ShiftLeft>, `TIMESTAMP`, <SquareRoot>, <CodeString>, <QuotedString>, <LiteralFloat>, <PGLiteralHex>, <LiteralInteger>, <MySQLLiteralHex>, `(`, `IS`, `IN`, `EXISTS`, `BETWEEN`, `+`, `-`, `*`, `/`, `//`, `DIV`, `%`, `||`, `<->`, `>`, `<`, `>=`, `<=`, `=`, `<>`, `!=`, `^`, `AND`, `LIKE`, <BitWiseOr>, <BitWiseAnd>, <BitWiseXor>, `->`, `->>`, `#>`, or 33 more ...


-- attempt 2 via CTE

with s as (select c0, c1 from source) update target set c1 = s.c1 where target.c0 = s.c0;
error: APIError: ResponseError with 1005: error: 
  --> SQL:1:39
  |
1 | with s as (select c0, c1 from source) update target set c1 = s.c1 where target.c0 = s.c0
  | ----                                  ^^^^^^ unexpected `update`, expecting `INSERT` or `,`
  | |                                      
  | while parsing `INSERT INTO [TABLE] <table> [(<column>, ...)] (FORMAT <format> | VALUES <values> | <query>)`

Are you willing to submit PR?

• [ ] Yes I am willing to submit a PR!

[inviscid]

@xudong963 and @zhyass - Mentioning you here since you worked on the original item referenced above.

[xudong963]

merge into is suitable for your case:

merge into target using source on target.c0 = source.c0 when matched then update set c1 = source.c1;

[rad-pat]

It would be, but currently not supported by SQLAlchemy and unlikely to be for some time (they already pushed it back like 10 years) so this is proving difficult. Update from subquery is fairly core SQL

[xudong963]

Got you, update from subquery is on my list

[xudong963]

I supported cte in update: https://github.com/datafuselabs/databend/pull/15276, though it won't solve your issue.

It only changed the error info for SQL: with s as (select c0, c1 from source) update target set c1 = s.c1 where target.c0 = s.c0 and the newest error info is expected.

such as

with tt1 as (select * from t1) update t2 set a = tt1.a;
----
mysql client error: Server error: `ERROR HY000 (1105): SemanticError. Code: 1065, Text = error: 
  --> SQL:1:54
  |
1 | with tt1 as (select * from t1) update t2 set a = tt1.a;
  |                                                      ^ column a doesn't exist

.'

[rad-pat]

OK, I see the change, many thanks. It's a step along the way. 🥳