为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】: dm8
【操作系统】:linux
【CPU】: x84
【问题描述】*:
在执行merge into 语句, 希望能得到具体执行的update动作还是insert动作;目前查看文档所知,merge语句并不支持返回值
CREATE TABLE IF NOT EXISTS person (
id bigint PRIMARY KEY AUTO_INCREMENT,
name character varying(50 char) default '',
age bigint default 0,
sex char(10) default 'm',
active tinyint default 0,
is_delete tinyint default 0,
constraint idx_test_name unique(name, sex)
);
MERGE INTO "person"
USING (SELECT 'test_1',21,'female',true,false FROM DUAL
UNION ALL SELECT 'test_2',22,'female',true,false FROM DUAL
UNION ALL SELECT 'test_4',23,'female',true,false FROM DUAL
UNION ALL SELECT 'test_3',23,'female',true,false FROM DUAL
) AS "excluded" ("name","age","sex","active","is_delete" ) ON "person"."name" = "excluded"."name" AND "person"."sex" = "excluded"."sex"
WHEN MATCHED THEN UPDATE SET "age"="excluded"."age","active"="excluded"."active"
WHEN NOT MATCHED THEN INSERT ("name","age","sex","active","is_delete") VALUES ("excluded"."name","excluded"."age","excluded"."sex","excluded"."active","excluded"."is_delete")
Oracle数据库中merge into 语句可支持 output子句,试了dm8发现不支持参考,如下
MERGE INTO "person"
USING (SELECT 'test_1',21,'female',true,false FROM DUAL
UNION ALL SELECT 'test_2',22,'female',true,false FROM DUAL
UNION ALL SELECT 'test_4',23,'female',true,false FROM DUAL
UNION ALL SELECT 'test_3',23,'female',true,false FROM DUAL
) AS "excluded" ("name","age","sex","active","is_delete" ) ON "person"."name" = "excluded"."name" AND "person"."sex" = "excluded"."sex"
WHEN MATCHED THEN UPDATE SET "age"="excluded"."age","active"="excluded"."active"
WHEN NOT MATCHED THEN INSERT ("name","age","sex","active","is_delete") VALUES ("excluded"."name","excluded"."age","excluded"."sex","excluded"."active","excluded"."is_delete")
OUTPUT $ACTION AS action
或者说数据库行记录中是否有对应的伪列(例如postgres的隐藏列xmax),可基于它实现所需的诉求?
sqlserver的语法吧。 12c/19c官方手册都没找到OUTPUT语法