数据库中更新或插入表记录

会碰到下列场景：当记录，在目标表中存在就更新值，若不存在就插入记录。

一、示例数据

-- test_source 数据源表

CREATE TABLE `test_source` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `BWKEY` varchar(255) DEFAULT NULL,
  `MATNR` varchar(255) DEFAULT NULL,
  `VERPR` double(255,0) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sBWKEY` (`BWKEY`,`MATNR`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

INSERT INTO `zjkywms`.`test_source`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (1, '2105', 'A', 1);
INSERT INTO `zjkywms`.`test_source`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (2, '2105', 'B', 2);
INSERT INTO `zjkywms`.`test_source`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (3, '2105', 'C', 3);
INSERT INTO `zjkywms`.`test_source`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (4, '2106', 'A', 4);
INSERT INTO `zjkywms`.`test_source`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (5, '2106', 'B', 5);
INSERT INTO `zjkywms`.`test_source`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (6, '2106', 'C', 6);

-- test_target 目标表/待更新或插入

CREATE TABLE `test_target` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `BWKEY` varchar(255) DEFAULT NULL,
  `MATNR` varchar(255) DEFAULT NULL,
  `VERPR` double(255,0) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tBWKEY` (`BWKEY`,`MATNR`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

INSERT INTO `zjkywms`.`test_target`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (1, '2105', 'A', 7);
INSERT INTO `zjkywms`.`test_target`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (2, '2106', 'C', 6);
INSERT INTO `zjkywms`.`test_target`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (3, '2107', 'Z', 9);

二、update + insert 二步操作

-- 待更新的记录
SELECT * from test_target t inner JOIN test_source s on s.BWKEY=t.BWKEY and s.MATNR=t.MATNR;
-- update test_target t inner JOIN test_source s on s.BWKEY=t.BWKEY and s.MATNR=t.MATNR set t.VERPR=s.VERPR

-- 待插入的记录
-- insert into test_target (BWKEY,MATNR,VERPR)
 SELECT s.BWKEY,s.MATNR,s.VERPR from test_source s where not exists (select 1 from test_target t where s.BWKEY=t.BWKEY and s.MATNR=t.MATNR);

效果

mysql> select * from test_target;
+----+-------+-------+-------+
| id | BWKEY | MATNR | VERPR |
+----+-------+-------+-------+
|  1 | 2105  | A     |     1 |
|  2 | 2106  | C     |     6 |
|  5 | 2107  | Z     |     9 |
|  6 | 2105  | B     |     2 |
|  7 | 2105  | C     |     3 |
|  8 | 2106  | A     |     4 |
|  9 | 2106  | B     |     5 |
+----+-------+-------+-------+
7 rows in set (0.03 sec)

记录ID：1是更新记录，原值是7；6~9是插入记录。

 

三、Oracle一步操作

-- merge into应用：实现 无则insert插入,有则update更新，一条语句直接进行insert/update操作

MERGE INTO table1 alias1
          USING (table2|view2|sub_query2) alias2
          ON (join condition)
      WHEN MATCHED THEN
          UPDATE table1
            SET col1 = col_val1,
                col2 = col2_val2
            [where ]
      WHEN NOT MATCHED THEN
                INSERT (column_list) VALUES (column_values)
                 [where ];

四、MySQL一步操作

方案1: insert into table (key) values (value) ON DUPLICATE KEY UPDATE key = value

    > 该方案要求有一个唯一索引,当插入的时候先判断,如果该字段存在数据,则更新该条数据,如果不存在则插入
    > 注意：当您使用 ON DUPLICATE KEY UPDATE 时，DELAYED选项被忽略（DELAYED 做为快速插入，并不是很关心时效性，提高插入性能）。

truncate test_target;
INSERT INTO `zjkywms`.`test_target`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (1, '2105', 'A', 7);
INSERT INTO `zjkywms`.`test_target`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (2, '2106', 'C', 6);
INSERT INTO `zjkywms`.`test_target`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (3, '2107', 'Z', 9);
select * from test_target;

insert into test_target (`BWKEY`, `MATNR`, `VERPR`) values ('2105', 'A', 1) ON DUPLICATE KEY UPDATE VERPR = VALUES(VERPR);
insert into test_target (`BWKEY`, `MATNR`, `VERPR`) values ('2106', 'X', 99) ON DUPLICATE KEY UPDATE VERPR = VALUES(VERPR);

效果

mysql> select * from test_target;
+----+-------+-------+-------+
| id | BWKEY | MATNR | VERPR |
+----+-------+-------+-------+
|  1 | 2105  | A     |     1 |
|  2 | 2106  | C     |     6 |
|  3 | 2107  | Z     |     9 |
|  5 | 2106  | X     |    99 |
+----+-------+-------+-------+
4 rows in set (0.03 sec)

记录ID：1是更新记录，原值是7；5是插入记录。

truncate test_target;
INSERT INTO `zjkywms`.`test_target`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (1, '2105', 'A', 7);
INSERT INTO `zjkywms`.`test_target`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (2, '2106', 'C', 6);
INSERT INTO `zjkywms`.`test_target`(`id`, `BWKEY`, `MATNR`, `VERPR`) VALUES (3, '2107', 'Z', 9);
select * from test_target;
insert into test_target (`BWKEY`, `MATNR`, `VERPR`) select `BWKEY`, `MATNR`, `VERPR` from test_source ON DUPLICATE KEY UPDATE VERPR = VALUES(VERPR);

效果

mysql> select * from test_target;
+----+-------+-------+-------+
| id | BWKEY | MATNR | VERPR |
+----+-------+-------+-------+
|  1 | 2105  | A     |     1 |
|  2 | 2106  | C     |     6 |
|  3 | 2107  | Z     |     9 |
|  4 | 2105  | B     |     2 |
|  5 | 2105  | C     |     3 |
|  6 | 2106  | A     |     4 |
|  7 | 2106  | B     |     5 |
+----+-------+-------+-------+
7 rows in set (0.04 sec)

记录ID：1是更新记录，原值是7；4~7是插入记录。

 

方案2: replace into table (key) values (value)

    > 1.尝试把新行插入到表中
    > 2. 当因为对于主键或唯一关键字出现重复关键字错误而造成插入失败时：
        > a. 从表中删除含有重复关键字值的冲突行
        > b. 再次尝试把新行插入到表中
    > 该方案会将之前的数据删除,然后重新插入新的数据.如果新的数据某个字段是空的,执行完replace into之后,该字段会变成字段的默认值