Oracle 迁移ADB/Greenplum之merge into 语法

Oracle 迁移ADB/Greenplum之merge into 语法

oracle迁移ADB过程中，碰到merge into（合并更新）的语法改造问题，ADB/Greenplum中不支持oracle的merge into，需要修改成ADB/Greenplum支持的INSERT ON CONFLICT覆盖写入的语法
示例演示：
Oracle语法

MERGE INTO schema. table alias
USING { schema. table | views | query} alias
ON {(condition) }
WHEN MATCHED THEN
  UPDATE SET  {clause}
WHEN NOT MATCHED THEN
  INSERT {column} VALUES  {clause};

解析
INTO 子句 --用于指定你所update或者Insert目的表。
USING 子句 --用于指定你要update或者Insert的记录的来源，它可能是一个表，视图，子查询。
ON Clause --用于目的表和源表（视图，子查询）的关联，如果匹配（或存在），则更新，否则插入。
merge_update_clause --用于写update语句
merge_insert_clause --用于写insert语句

创建两个表，CUSTOMERS_HIS , CUSTOMERS_NOW

-- 创建测试表  CUSTOMERS_HIS
CREATE TABLE CUSTOMERS_HIS (
   customer_id VARCHAR2(20),
   customer_name VARCHAR2(50),
   deposit    NUMBER,
   amount   NUMBER
);
--插入数据
INSERT INTO CUSTOMERS_HIS VALUES ('KH0001','王一',10000,1000.5);
INSERT INTO CUSTOMERS_HIS VALUES('KH0002','刘二',1030,9405.2);
INSERT INTO CUSTOMERS_HIS VALUES('KH0003','张三',2400,66);
INSERT INTO CUSTOMERS_HIS VALUES('KH0004','李四',65000,65445);
INSERT INTO CUSTOMERS_HIS VALUES('KH0005','王五',600,4324);
INSERT INTO CUSTOMERS_HIS VALUES('KH0006','张六',95200,4123.4);
INSERT INTO CUSTOMERS_HIS VALUES('KH0007','李七',672800,1231.2);
INSERT INTO CUSTOMERS_HIS VALUES('KH0008','李八',10230,13200.5);
INSERT INTO CUSTOMERS_HIS VALUES('KH0009','刘九',10,2320.5);

-- 创建测试表  CUSTOMERS_NOW
CREATE TABLE CUSTOMERS_NOW  AS SELECT * FROM CUSTOMERS_HIS WHERE 1=2; 
--插入数据
INSERT INTO CUSTOMERS_NOW VALUES ('KH0001','王一',300,1000.5);
INSERT INTO CUSTOMERS_NOW VALUES('KH0002','刘二',1030,9405.2);
INSERT INTO CUSTOMERS_NOW VALUES('KH0003','张三',2400,66);
INSERT INTO CUSTOMERS_NOW VALUES('KH0004','李四',1,65445);
INSERT INTO CUSTOMERS_NOW VALUES('KH0005','张三丰',20,4324);
INSERT INTO CUSTOMERS_NOW VALUES('KH0006','张无忌',20,4123.4);
INSERT INTO CUSTOMERS_NOW VALUES('KH0010','赵敏',1430,1231.2);
INSERT INTO CUSTOMERS_NOW VALUES('KH0011','谢逊',130,13200.5);
INSERT INTO CUSTOMERS_NOW VALUES('KH0012','路人甲',10,2320.5);
--查询两个表的数据
SELECT * FROM CUSTOMERS_HIS;
SELECT * FROM CUSTOMERS_NOW;

-- 使用merge into 修改表,关联customer_id字段
MERGE INTO CUSTOMERS_HIS H
USING (SELECT customer_id,customer_name,deposit,amount FROM CUSTOMERS_NOW ) N
ON (H.customer_id = N.customer_id)
WHEN MATCHED THEN 
  UPDATE SET H.customer_name = N.customer_name ,H.deposit=N.deposit,H.amount =N.amount 
WHEN NOT MATCHED THEN
  INSERT	(H.customer_id,H.customer_name,H.deposit,H.amount) VALUES(N.customer_id,N.customer_name,N.deposit,N.amount);
-- 再次查询CUSTOMERS_HIS表，数据被合并更新

Greenplum/ADB INSERT ON CONFLICT覆盖写入
覆盖写入语法基于INSERT语句，INSERT语句的语法大纲如下：

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

其中，conflict_target为:
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, …] )
其中，conflict_action为:
DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, …] ) = ( { expression | DEFAULT } [, …] )
} [, …]
[ WHERE condition ]

相对于普通INSERT语法，覆盖写主要增加了ON CONFLICT子句，该子句分为两部分，分别是：
conflict_target，用于指定在哪些列上有冲突。conflict_target在conflict_action为DO NOTHING时可省略，在conflict_action为DO UPDATE时，需要指定一个列表，指定主键列的列表或Unique Index列的列表。
conflict_action，用于指定冲突后需要执行的动作。分为DO NOTHING和DO UPDATE两种。 （1）DO NOTHING表示如果有冲突，则丢弃待插入的数据。 （2）DO UPDATE表示如果有冲突，则按照后面的UPDATE语法进行数据覆盖。

约束和需要注意的地方：
⭐ 目标表需为行存表，不支持列存表（列存不支持unique index，所以无法支持列存表）
⭐ 目标表不支持分区表
⭐ 不支持在UPDATE的SET子句中更新分布列和主键列
⭐ 不支持在UPDATE的WHERE子句中使用子查询
⭐ 目标表不支持Updatable View
⭐ 不支持在同一条INSERT语句中对同一主键插入多条数据（国际SQL标准约束）

然像上面的示例一样，新建两个表CUSTOMERS_HIS , CUSTOMERS_NOW。需要注意的是，插入更新目标表的对比字段**（ON CONFLICT后的列名）必须是PK**。

-- 创建测试表  CUSTOMERS_HIS
CREATE TABLE CUSTOMERS_HIS (
customer_id VARCHAR(20) primary key,	
   customer_name VARCHAR(50),
   deposit    NUMERIC,
   amount   NUMERIC
) DISTRIBUTED BY (customer_id);

--插入数据
INSERT INTO CUSTOMERS_HIS VALUES ('KH0001','王一',10000,1000.5);
INSERT INTO CUSTOMERS_HIS VALUES('KH0002','刘二',1030,9405.2);
INSERT INTO CUSTOMERS_HIS VALUES('KH0003','张三',2400,66);
INSERT INTO CUSTOMERS_HIS VALUES('KH0004','李四',65000,65445);
INSERT INTO CUSTOMERS_HIS VALUES('KH0005','王五',600,4324);
INSERT INTO CUSTOMERS_HIS VALUES('KH0006','张六',95200,4123.4);
INSERT INTO CUSTOMERS_HIS VALUES('KH0007','李七',672800,1231.2);
INSERT INTO CUSTOMERS_HIS VALUES('KH0008','李八',10230,13200.5);
INSERT INTO CUSTOMERS_HIS VALUES('KH0009','刘九',10,2320.5);

-- 创建测试表  CUSTOMERS_NOW
CREATE TABLE CUSTOMERS_NOW  (LIKE CUSTOMERS_HIS); 
--插入数据
INSERT INTO CUSTOMERS_NOW VALUES ('KH0001','王一',300,1000.5);
INSERT INTO CUSTOMERS_NOW VALUES('KH0002','刘二',1030,9405.2);
INSERT INTO CUSTOMERS_NOW VALUES('KH0003','张三',2400,66);
INSERT INTO CUSTOMERS_NOW VALUES('KH0004','李四',1,65445);
INSERT INTO CUSTOMERS_NOW VALUES('KH0005','张三丰',20,4324);
INSERT INTO CUSTOMERS_NOW VALUES('KH0006','张无忌',20,4123.4);
INSERT INTO CUSTOMERS_NOW VALUES('KH0010','赵敏',1430,1231.2);
INSERT INTO CUSTOMERS_NOW VALUES('KH0011','谢逊',130,13200.5);
INSERT INTO CUSTOMERS_NOW VALUES('KH0012','路人甲',10,2320.5);

--查询数据
SELECT * FROM CUSTOMERS_HIS;
SELECT * FROM CUSTOMERS_NOW;

-- 使用 INSERT INTO CONFLICT 修改表
在DO UPDATE SET子句中，可以使用EXCLUDED来表示冲突的数据构成的伪表
INSERT INTO CUSTOMERS_HIS (customer_id,customer_name,deposit,amount)
SELECT customer_id,customer_name,deposit,amount FROM CUSTOMERS_NOW 
ON CONFLICT (customer_id)  
DO UPDATE SET customer_name = excluded.customer_name,deposit = excluded.deposit,
amount = excluded.amount;
-- 再次查询CUSTOMERS_HIS表的数据

-- 排序查询CUSTOMERS_HIS表的数据做对比
SELECT * FROM CUSTOMERS_HIS ORDER BY customer_id;

对比两次数据结果，不考虑效率问题，结果是一致的。
如果customer_id字段不是primary key,语法会报错