weixin_46724576
2021-11-29 17:04
采纳率: 100%
浏览 9

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

报错样例

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条回答 默认 最新

  • 生命之源 0 2021-11-29 17:05
    已采纳

    首先从业务上要避免这种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。

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题