为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】: dm8_20220822_rev166351_x86_rh6_64_ctm.tar
【操作系统】:masos
【CPU】: Intel
【问题描述】*:
create table "task"(
id int auto_increment primary key,
name varchar(30) not null default '',
success_num int not null default 0,
fail_num int not null default 0
);
insert into task(name,success_num, fail_num)
values ('任务1', 2, 1), ('任务2', 4, 3);
在mysql中,批量操作,可以用如下语句
INSERT INTO task (id, name, success_num, fail_num)
VALUES (1, '任务1', 20, 10),
(3, '任务3', 5, 4)
ON DUPLICATE KEY
UPDATE success_num =
VALUES (success_num), fail_num =
VALUES (fail_num);
达梦中,只能用MERGE INTO
单条操作吗?
MERGE INTO task USING dual ON (id = 1)
WHEN MATCHED THEN update set success_num = 20, fail_num = 10
WHEN NOT MATCHED THEN insert (id, name, success_num, fail_num) values (1, '任务11', 20, 10);
MERGE INTO task USING dual ON (id = 3)
WHEN MATCHED THEN update set success_num = 5, fail_num = 4
WHEN NOT MATCHED THEN insert (id, name, success_num, fail_num) values (3, '任务3', 5, 4);
已解决
MERGE INTO task T1 USING (SELECT 1 as id, '任务11' as name, 20 as success_num, 10 as fail_num FROM DUAL UNION ALL SELECT 3 as id, '任务3' as name, 5 as success_num, 4 as fail_num) T2 ON (T1.id = T2.id) WHEN NOT MATCHED THEN INSERT (id, name, success_num, fail_num) VALUES (T2.id, T2.name, T2.success_num, T2.fail_num) WHEN MATCHED THEN UPDATE SET T1.success_num = T2.success_num, T1.fail_num = T2.fail_num;