[Feature Request]: UPDATE/DELETE multiple tables

[dengn]

Is there an existing issue for the same feature request?

• [X] I have checked the existing issues.

Is your feature request related to a problem?

0.5 doesn't support this feature yet.

Describe the feature you'd like

Update/Delete from multiple table, and support also multiple table join.

Some examples:

DELETE t1 FROM t1 LEFT JOIN T2 ON t1.id=t2.id WHERE t2.id IS NULL

UPDATE product p LEFT JOIN productPrice pp ON p.productId = pp.productId SET p.deleted = 1 WHERE pp.productId IS null

update b set memo = a.name from a,b where a.id = b.id

update dw_mt1 a set lowarpu_quarter1 = b.lowarpu_quarter1, lowarpu_quarter2= b.lowarpu_quarter2, lowarpu_quarter3 = b.lowarpu_quarter3, lowarpu_quarter4 = b.lowarpu_quarter4, income_flag = b.income_flag, useroff_flag = b.useroff_flag, cmccoverlay_rate = b.cmccoverlay_rate, overlay_flag = b.overlay_flag, status_date = b.status_date from (select d.oid, c.lowarpu_quarter1, c.lowarpu_quarter2, c.lowarpu_quarter3, c.lowarpu_quarter4, c.income_flag, c.useroff_flag, c.cmccoverlay_rate, c.overlay_flag, c.status_date from dw_mt1 d inner join pg_ent_staticphoto_mt_arpu c on d.enterprise_id = c.enterprise_id) b where a.oid = b.oid

Describe implementation you've considered

No response

Documentation, Adoption, Use Case, Migration Strategy

No response

Additional information

No response

[iamlinjunhong]

1. multiple table delete has done
2. multiple table update will finish on Jul 23, 2022 except update to default value
3. update to default value will finish after issue #3202

[iamlinjunhong]

2 will finish on Jul 27, 2022, because interface of update that tae offers cancel.

[iamlinjunhong]

Differences between Mo update and MySQL update:

1. The affectRows returned after update are different from mysql, for example:

   create table t1(a int);
   insert into t1 values(1), (1), (1);
   update t1 set a = 1;
   
   mysql: affectRows = 0
   mo: affectRows = 3
   

2. It is not allowed to update a column multiple times (the same PG) in the same column, such as

   update t1 set a = 1, a = 2;  // Mo will report an error
   

3. Mo update updates on the basis of old data (the same as PG), which is different from mysql that updates from left to right, such as:

   create table t1 (a int, b int);
   insert into t1 values(1, 2);
   update t1 set a = 2, b = a + 1;
   select * from t1;
   
   mo:
   a b
   2 2
   
   mysql:
   a b
   2 3
   

[goodMan-code]

some cases has been added to test/cases/dml/delete/delete_multiple_table.sql

[goodMan-code]

This Feature Request has done, close it pls