update语法支持关联表更新吗4.3.0版本

OceanBase 技术问题
#1

【 使用环境 】 测试环境
【 OB or 其他组件 】OB
【 使用版本 】4.3.0
【问题描述】测试update关联表更新语法报错
【复现路径】
mysql8可行语法：
update db_test.t_order a
inner join db_test.t_dim_avgkey b
on a.avgkeystr = b.avgkeystr
set a.aint = a.aint + 1,a.dstr = concat(substr(b.dstr,0,10),‘change by dim’)
where a.ggdatetime >= ‘2023-04-01’ and a.ggdatetime < ‘2023-05-01’
and b.avgkeyname = ‘gqazijs’
;

根据文档改成ob语法报错：（只提示语法错，不知道是错在哪里）
update db_test.t_order a,db_test.t_dim_avgkey b
set a.aint = a.aint + 1,a.dstr = concat(substr(b.dstr,0,10),‘change by dim’)
where a.avgkeystr = b.avgkeystr
and a.ggdatetime >= ‘2023-04-01’ and a.ggdatetime < ‘2023-05-01’
and b.avgkeyname = ‘gqazijs’
;

#3

麻烦提供一下（脱敏后的）相关表结构？

#4

大佬看看如下：
CREATE TABLE db_test.t_order (
order_no varchar(32) NOT NULL comment ‘订单号’,
avgkeystr varchar(32) comment ‘均匀分布字段’,
skewkeystr varchar(40) comment ‘倾斜分布字段’,
hbasestr varchar(32) comment ‘高基数字段’,
lbasestr varchar(32) comment ‘低基数字段’,
status int comment ‘0,初始化、1,成功、2,失败、3,已发送、4,已接收、5,已拒绝、6,中止’,
aint int,
bdouble double,
cdec numeric(16,4),
dstr varchar(64),
etext text,
fday DATE,
gtimestamp timestamp,
ggdatetime datetime,
hjson json,
ifield varchar(64),
jfield varchar(64),
kfield varchar(64),
lfield varchar(64),
mfield varchar(64),
create_time datetime,
update_time datetime,
remark varchar(255),
index idx_order_no_ggdatetime (order_no,ggdatetime)
)
COMMENT ‘订单表’
PARTITION BY RANGE (TO_DAYS(ggdatetime)) (
PARTITION p202301 VALUES LESS THAN (TO_DAYS(‘2023-02-01’)),
PARTITION p202302 VALUES LESS THAN (TO_DAYS(‘2023-03-01’)),
PARTITION p202303 VALUES LESS THAN (TO_DAYS(‘2023-04-01’)),
PARTITION p202304 VALUES LESS THAN (TO_DAYS(‘2023-05-01’)),
PARTITION p202305 VALUES LESS THAN (TO_DAYS(‘2023-06-01’)),
PARTITION p202306 VALUES LESS THAN (TO_DAYS(‘2023-07-01’)),
PARTITION p202307 VALUES LESS THAN (TO_DAYS(‘2023-08-01’)),
PARTITION p202308 VALUES LESS THAN (TO_DAYS(‘2023-09-01’)),
PARTITION p202309 VALUES LESS THAN (TO_DAYS(‘2023-10-01’)),
PARTITION p202310 VALUES LESS THAN (TO_DAYS(‘2023-11-01’)),
PARTITION p202311 VALUES LESS THAN (TO_DAYS(‘2023-12-01’)),
PARTITION p202312 VALUES LESS THAN (TO_DAYS(‘2024-01-01’)),
PARTITION p202401 VALUES LESS THAN (TO_DAYS(‘2024-02-01’)),
PARTITION p202402 VALUES LESS THAN (TO_DAYS(‘2024-03-01’)),
PARTITION p202403 VALUES LESS THAN (TO_DAYS(‘2024-04-01’)),
PARTITION p202404 VALUES LESS THAN (TO_DAYS(‘2024-05-01’)),
PARTITION p202405 VALUES LESS THAN (TO_DAYS(‘2024-06-01’)),
PARTITION p202406 VALUES LESS THAN (TO_DAYS(‘2024-07-01’))
)
;

CREATE TABLE db_test.t_dim_avgkey (
avgkeystr varchar(32) comment ‘均匀分布字段’,
avgkeyname varchar(64) comment ‘属性名’,
category_no varchar(32) comment ‘类别编号’,
aint int,
bdouble double,
cdec numeric(16,4),
dstr varchar(64),
fday DATE,
ggdatetime datetime,
create_time datetime,
update_time datetime,
create_user varchar(64),
update_user varchar(64),
physics_flag int,
remark varchar(255),
primary key (avgkeystr)
)
;

#5

测试了没有问题，应该是你自己那边的输入法或者引号有问题。

image
image1076×1222 296 KB 

CREATE TABLE db_test.t_order (
order_no varchar(32) NOT NULL comment '订单号',
avgkeystr varchar(32) comment '均匀分布字段',
skewkeystr varchar(40) comment '倾斜分布字段',
hbasestr varchar(32) comment '高基数字段',
lbasestr varchar(32) comment '低基数字段',
status int comment '0,初始化、1,成功、2,失败、3,已发送、4,已接收、5,已拒绝、6,中止',
aint int,
bdouble double,
cdec numeric(16,4),
dstr varchar(64),
etext text,
fday DATE,
gtimestamp timestamp,
ggdatetime datetime,
hjson json,
ifield varchar(64),
jfield varchar(64),
kfield varchar(64),
lfield varchar(64),
mfield varchar(64),
create_time datetime,
update_time datetime,
remark varchar(255),
index idx_order_no_ggdatetime (order_no,ggdatetime)
)
COMMENT '订单表'
PARTITION BY RANGE (TO_DAYS(ggdatetime)) (
PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')),
PARTITION p202305 VALUES LESS THAN (TO_DAYS('2023-06-01')),
PARTITION p202306 VALUES LESS THAN (TO_DAYS('2023-07-01')),
PARTITION p202307 VALUES LESS THAN (TO_DAYS('2023-08-01')),
PARTITION p202308 VALUES LESS THAN (TO_DAYS('2023-09-01')),
PARTITION p202309 VALUES LESS THAN (TO_DAYS('2023-10-01')),
PARTITION p202310 VALUES LESS THAN (TO_DAYS('2023-11-01')),
PARTITION p202311 VALUES LESS THAN (TO_DAYS('2023-12-01')),
PARTITION p202312 VALUES LESS THAN (TO_DAYS('2024-01-01')),
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-06-01')),
PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-07-01'))
);

CREATE TABLE db_test.t_dim_avgkey (
avgkeystr varchar(32) comment '均匀分布字段',
avgkeyname varchar(64) comment '属性名',
category_no varchar(32) comment '类别编号',
aint int,
bdouble double,
cdec numeric(16,4),
dstr varchar(64),
fday DATE,
ggdatetime datetime,
create_time datetime,
update_time datetime,
create_user varchar(64),
update_user varchar(64),
physics_flag int,
remark varchar(255),
primary key (avgkeystr)
);

update db_test.t_order a,db_test.t_dim_avgkey b
set a.aint = a.aint + 1,a.dstr = concat(substr(b.dstr,0,10),'change by dim')
where a.avgkeystr = b.avgkeystr
and a.ggdatetime >= '2023-04-01' and a.ggdatetime < '2023-05-01'
and b.avgkeyname = 'gqazijs';