mysql marge into_MySql实现Merge Into 功能代码

需求描述

INSERT ... ON DUPLICATE KEY UPDATE Syntax

If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

INSERT INTO t1 (a,b,c) VALUES (1,2,3)

ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

实现内容源mds_user_agent 和 MDS_USER_CHATBOT 当Email 号相同则合并数据，并更新MDS_USER_SUMMARY_INFO 否则则更新 同时。1.如果目标表字段不为空而源为空则不更新。--创建表

CREATE TABLE mds_user_agent

(

ID MEDIUMINT NOT NULL AUTO_INCREMENT,

CHATID VARCHAR(50),

EMAILADDRESS VARCHAR(50),

DATEOFFIRSTAGENTENGAGEMENT DATETIME ,

PRODUCTID VARCHAR(50),

DATE DATETIME,

PRIMARY KEY (ID)

)

--插入测试数据

INSERT INTO mds_user_agent VALUES (1,'Dxxxhah001','45678@qq.com','2018-03-16 10:26:09','1079','2018-03-20 10:26:09')

INSERT INTO mds_user_agent VALUES (2,'Dxxxhah002','45679@qq.com','2018-03-16 10:34:09','1078','2018-03-20 10:26:10')

--创建表

CREATE TABLE MDS_USER_CHATBOT

(

ID MEDIUMINT NOT NULL AUTO_INCREMENT,

USERID VARCHAR(50),

LASTNAME VARCHAR(50),

FIRSTNAME VARCHAR(50),

AGE INT,

GENDER VARCHAR(50),

OCCUPATION VARCHAR(50),

EMAIL VARCHAR(50),

COUNTRY VARCHAR(50),

FIRST_CREATE_DATE DATETIME,

DATE DATETIME,

PRIMARY KEY (ID))

INSERT INTO MDS_USER_CHATBOT VALUES (1,'9527','Zhang','DaBen',40,'Man','Gobudongshayisi','45678@qq.com','China','2018-03-21 10:26:09','2018-03-20 10:46:09')

INSERT INTO MDS_USER_CHATBOT VALUES (2,'0352','Jia','XiaoMing',21,'Women','Gobudongshayisi','45676@qq.com','China','2018-03-19 10:26:09','2018-03-20 10:56:09')

--目标表创建

CREATE TABLE MDS_USER_SUMMARY_INFO

(

USERID ,

LASTNAME VARCHAR(50),

FIRSTNAME VARCHAR(50),

AGE INT,

GENDER VARCHAR(50),

OCCUPATION VARCHAR(50),

EMAIL VARCHAR(50),

PHONE_NUMBER INT,

WECHAT_ID VARCHAR(50),

FACEBOOK_ID VARCHAR(50),

IMEI_NUMBER VARCHAR(50),

SN_NUMBER VARCHAR(50),

ADDRESS VARCHAR(50),

LENOVOID VARCHAR(50),

MOTOROLAID VARCHAR(50),

PRODUCTID VARCHAR(50),

COUNTRY VARCHAR(50),

CITY VARCHAR(50),

FIRST_CREATE_DATE DATETIME,

DATE DATETIME )

--创建唯一索引

ALTER TABLE MDS_USER_SUMMARY_INFO ADD UNIQUE (EMAIL)

实现代码

INSERT INTO MDS_USER_SUMMARY_INFO

(USERID,

LASTNAME,

FIRSTNAME,

AGE,

GENDER,

OCCUPATION,

EMAIL,

PHONE_NUMBER,

WECHAT_ID,

FACEBOOK_ID,

IMEI_NUMBER,

SN_NUMBER,

ADDRESS,

LENOVOID,

MOTOROLAID,

PRODUCTID,

COUNTRY,

CITY,

FIRST_CREATE_DATE,

DATE)

SELECT LAST_INSERT_ID(), /*序列号*/

C.LASTNAME AS LASTNAME,

C.FIRSTNAME AS FIRSTNAME,

C.AGE AS AGE,

C.GENDER AS GENDER,

C.OCCUPATION AS OCCUPATION,

C.EMAIL AS EMAIL,

5050132 AS PHONE_NUMBER,

'MAKASHI001' AS WECHAT_ID,

'8765421' AS FACEBOOK_ID,

'SN3245698254' AS IMEI_NUMBER,

'SN32456982540325' SN_NUMBER,

'NIUYUEMADAHA' AS ADDRESS,

'5489624' AS LENOVOID,

'36952148' AS MOTOROLAID,

C.PRODUCTID AS PRODUCTID,

C.COUNTRY AS COUNTRY,

'DATONG' AS CITY,

CASE

WHEN DATEOFFIRSTAGENTENGAGEMENT >= FIRST_CREATE_DATE THEN

FIRST_CREATE_DATE

ELSE

DATEOFFIRSTAGENTENGAGEMENT

END FIRST_CREATE_DATE,

NOW() AS DATE /*获取系统当前时间*/

FROM (SELECT IFNULL(A.EMAILADDRESS, B.EMAIL) AS EMAIL,

A.PRODUCTID,

A.DATEOFFIRSTAGENTENGAGEMENT,

B.LASTNAME,

B.FIRSTNAME,

B.AGE,

B.GENDER,

B.COUNTRY,

B.EMAIL AS EMAIL01,

B.OCCUPATION,

B.FIRST_CREATE_DATE

FROM MDS_USER_AGENT A

RIGHT JOIN MDS_USER_CHATBOT B ON A.EMAILADDRESS = B.EMAIL

UNION

SELECT IFNULL(A.EMAILADDRESS, B.EMAIL) AS EMAIL,

A.PRODUCTID,

A.DATEOFFIRSTAGENTENGAGEMENT,

B.LASTNAME,

B.FIRSTNAME,

B.AGE,

B.GENDER,

B.COUNTRY,

B.EMAIL AS EMAIL01,

B.OCCUPATION,

B.FIRST_CREATE_DATE

FROM MDS_USER_AGENT A

LEFT JOIN MDS_USER_CHATBOT B ON A.EMAILADDRESS = B.EMAIL) C

ON DUPLICATE KEY UPDATE

PRODUCTID = IF(VALUES(PRODUCTID) IS NOT NULL,

VALUES(PRODUCTID),

MDS_USER_SUMMARY_INFO.PRODUCTID),

LASTNAME = IF(VALUES(LASTNAME) IS NOT NULL,

VALUES(LASTNAME),

MDS_USER_SUMMARY_INFO.LASTNAME),

FIRSTNAME = IF(VALUES(FIRSTNAME) IS NOT NULL,

VALUES(FIRSTNAME),

MDS_USER_SUMMARY_INFO.FIRSTNAME),

GENDER = IF(VALUES(GENDER) IS NOT NULL,

VALUES(GENDER),

MDS_USER_SUMMARY_INFO.GENDER),

OCCUPATION = IF(VALUES(OCCUPATION) IS NOT NULL,

VALUES(OCCUPATION),

MDS_USER_SUMMARY_INFO.OCCUPATION),

FIRST_CREATE_DATE =

IF(VALUES(FIRST_CREATE_DATE) IS NOT NULL,

VALUES(FIRST_CREATE_DATE),

MDS_USER_SUMMARY_INFO.FIRST_CREATE_DATE);