匿名用户报错样例
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
复制
```
首先从业务上要避免这种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:
gbase> select * from t1;
+------+-------+
| id | value |
+------+-------+
| 1 | 234 |
+------+-------+
1 row in set (Elapsed: 00:00:00.00)
复制
对于多表关联更新，无解。请调整业务或改写SQL。
评论
有用 1
