GBase 8a执行update SQL报错无法更新多行数据 can not update one row to multi-data

Question

报错样例

gbase> create table t1(id int, value int);
Query OK, 0 rows affected (Elapsed: 00:00:00.18)

gbase> insert into t1 values(1,'0');
Query OK, 1 row affected (Elapsed: 00:00:00.07)

gbase> create table t2(id int, value int);
Query OK, 0 rows affected (Elapsed: 00:00:00.10)

gbase> insert into t2 values(1,123);
Query OK, 1 row affected (Elapsed: 00:00:00.07)

gbase> insert into t2 values(1,234);
Query OK, 1 row affected (Elapsed: 00:00:00.07)

gbase> select * from t1;
+------+-------+
| id   | value |
+------+-------+
|    1 |     0 |
+------+-------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from t2;
+------+-------+
| id   | value |
+------+-------+
|    1 |   123 |
|    1 |   234 |
+------+-------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> update t1 set value=(select value from t2 where t2.id=t1.id);
ERROR 1709 (HY000): [10.0.2.107:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: (GBA-01EX-0002) Subquery return more than 1 row
SQL: /*::ffff:10.0.2.107_118_18_2020-09-03_20:47:02*/ UPDATE `testdb`.`t1_n1` `testdb.t1` SET `testdb.t1`.`value` = ( SELECT `testdb.t2`.`value` as `value` From  `gctmpdb`.`_tmp_1795293194_118_t51_1_1599096136_s` `testdb.t2` WHERE (`testdb.t2`.`id` = `testdb.t1`.`id`));
gbase> update t1,t2 set t1.value=t2.value where t1.id=t2.id;
ERROR 1709 (HY000): [10.0.2.107:5050](GBA-02AD-0005)Failed to query in gnode:
DETAIL: (GBA-01EX-700) Gbase general error: can not update one row to multi-data
SQL: /*::ffff:10.0.2.107_118_19_2020-09-03_20:47:28*/ UPDATE `gctmpdb`._tmp_1795293194_118_t52_1_1599096136_s INNER JOIN `testdb`.`t1_n1` `testdb.t1` ON (`testdb.t1`.`id` = `_tmp_1795293194_118_t52_1_1599096136_s`.`id`) SET `testdb.t1`.`value` = `_tmp_1795293194_118_t52_1_1599096136_s`.`testdb.t1.value`;
gbase>
```bash

```

Answer 1

首先从业务上要避免这种1：n更新的情况。

如果实在避免不了，对于子查询，可以用limit限定结果集。比如

gbase> update t1 set value=(select value from t2 where t2.id=t1.id limit 1);
Query OK, 1 row affected (Elapsed: 00:00:00.28)
Rows matched: 1  Changed: 1  Warnings: 0

gbase> select * from t1;
+------+-------+
| id   | value |
+------+-------+
|    1 |   234 |
+------+-------+
1 row in set (Elapsed: 00:00:00.00)

对于多表关联更新，无解。请调整业务或改写SQL。