ON DUPLICATE KEY UPDATE添加或更新
首先主键是索引
insert into ys_yssf_rj
(customerId,settleDate,settleMoney,
settleActual, settleBalance,settleAccountState)
VALUES
(#customerId#,now(),#settleMoney#,
#settleActual#,#settleBalance#,#settleAccountState#
)
ON DUPLICATE KEY UPDATE
settleDate = now(),
settleMoney = #settleMoney#,
settleActual = #settleActual#,
settleBalance = #settleBalance#,
settleAccountState = #settleAccountState#
以前的做法是先根据主键去查找是否存在记录,找到就update,找不到就insert。需要写两条语句
on duplicate key update 的写法,很简单的解决了上面的问题,而且只需要写一条语句。
在给个例子,找得到就更新count自增
INSERT SPEAK_COUNT
SET participant_id = #{
participantId },
meeting_id = #{ meetingId },
agenda_id = #{ agendaId },
count = 1
ON DUPLICATE KEY UPDATE count = count + 1