MySQL插入数据时，如果记录不存在则insert，如果存在则update

第一步，将数据表不能重复的字段设置唯一索引(例如pro_name不能重复则为其设置唯一索引)

 

第二步，运行以下参考语句

 

 

/**************************从预算获取******************************/

/*INSERT INTO bus_pro_base_info  

(id, `subject_code`, `subjectl_name`, `office_name`, `pro_name`, `pro_code`, `budgetary_money`, `review_money`, org_id, org, dept_id, dept_name, attribute01, create_date, create_by) 

SELECT 

REPLACE(UUID(),'-',''), bu.subject_code, bu.subjectl_name, bu.office_name, bu.pro_name, bu.pro_code, bu.review_value, bu.reviewed_value, 

bu.org_id, bu.org, bu.dept_id, bu.dept_name, "来自预算", bu.create_date, bu.create_by 

FROM bus_pro_budget_approval bu

on duplicate key update 

subject_code = bu.subject_code, subjectl_name = bu.subjectl_name, office_name = bu.office_name, pro_name = bu.pro_name, pro_code = bu.pro_code, 

budgetary_money = bu.review_value, review_money = bu.reviewed_value, org_id = bu.org_id, org_id = bu.org_id, 

org = bu.org, dept_id = bu.dept_id, dept_name = bu.dept_name, attribute01 = "来自预算";*/

 

/**************************从结算算获取******************************/

/*INSERT INTO bus_pro_base_info  

(id, `subject_code`, `subjectl_name`, `office_name`, `pro_name`, `pro_code`, `company`, `check_money`, final_money, org_id, org, dept_id, dept_name, attribute02, create_date, create_by) 

SELECT 

REPLACE(UUID(),'-',''), c.subject_code, c.subjectl_name, c.office_name, c.pro_name, c.pro_code, c.build_unit, c.subtract_money, c.final_money,

c.org_id, c.org, c.dept_id, c.dept_name, "来自结算", c.create_date, c.create_by 

FROM bus_pro_closeapprove c

on duplicate key update 

subject_code = c.subject_code, subjectl_name = c.subjectl_name, office_name = c.office_name, pro_name = c.pro_name, pro_code = c.pro_code, 

company = c.build_unit, check_money = c.subtract_money, final_money = c.final_money, org_id = c.org_id, attribute02 = "来自结算";*/

 

/*SELECT count(*) FROM bus_pro_base_info WHERE attribute01 = '来自预算' ;*/

/*SELECT * FROM bus_pro_base_info WHERE attribute01 IS NULL && attribute02 IS NULL;*/

/*SELECT * FROM bus_pro_base_info WHERE attribute01 IS  NOT NULL OR attribute02 IS NOT NULL;*/