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

青旅半醒 2022-10-27 05:13 196阅读 0赞

第一步，将数据表不能重复的字段设置唯一索引(例如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;\*/