需求描述
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);
