批量更新
方法1:
UPDATE acc_device_group
SET uuid=tmp.uuid,group_name=tmp.group_name ,create_user=tmp.create_user,update_user=tmp.update_user,court_uuid=tmp.court_uuid,remark=tmp.remark ,delete_flag=tmp.delete_flag
FROM (VALUES ('7fa6b9d2483c410a86aa94a22a873633', '用户体验测试', 'admin', 'admin', '123465', '用户体验', 1) ,
('e588b58f54e045c1a670ea17b86d1927', '用户体验测试', 'admin', 'admin', '123465', '用户体验', 1) )
AS tmp(uuid, group_name, create_user,update_user,court_uuid,remark,delete_flag)
WHERE acc_device_group.uuid = tmp.uuid
方法2:
update acc.acc_device_group
set create_user =case
when uuid='966310efbf4447b7b810e5704b5cd303' then 'admin1'
when uuid='76de8324c6bc4841aa82c9f305e49d13' then 'admin2'
end,
update_user =case
when uuid='966310efbf4447b7b810e5704b5cd303' then 'admin1'
when uuid='76de8324c6bc4841aa82c9f305e49d13' then 'admin2'
end
where uuid in ( '966310efbf4447b7b810e5704b5cd303' , '76de8324c6bc4841aa82c9f305e49d13' )
mybatis映射
<update id="batchUpdate1" parameterType="java.util.List">
update acc.acc_device_group
<trim prefix="set" suffixOverrides=",">
<trim prefix="create_user =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.createUser !=null">
when uuid=#{item.uuid} then #{item.createUser}
</if>
</foreach>
</trim>
<trim prefix="update_user =case" suffix="end,">
<foreach collection="list" item="item" index="index">
<if test="item.updateUser !=null">
when uuid=#{item.uuid} then #{item.updateUser}
</if>
</foreach>
</trim>
</trim>
where uuid in
<foreach collection="list" index="index" item="item"
separator="," open="(" close=")">
#{item.uuid}
</foreach>
</update>
单条记录避免重复插入,当有重复时,改为更新,或不改动。uuid为主键
insert into acc_device_group(uuid, group_name,create_time,update_time,create_user,update_user,court_uuid,remark,delete_flag)
values ('7fa6b9d2483c410a86aa94a22a873636','用户体验测试','2018-01-22 16:13:32.65','2018-01-22 16:13:32.65','admin','admin','11','用户体验',1))
on conflict (uuid)
do update SET
group_name=tmp(group_name),create_time='2018-01-22 16:13:32.65',update_time='2018-01-22 16:13:32.65',create_user='admin',update_user='admin',court_uuid='11',remark='用户体验',delete_flag=0
不改动只要把后面改为do nothing
根据不是主键或唯一的字段判断是否重复:
INSERT INTO acc_device_auth(uuid, create_time, update_time, create_user, update_user,
court_uuid,start_date, end_date,user_id, user_type,delete_flag,enable_flag)
SELECT '8c81b411bed349f9975154ce6d66636','2018-01-22 16:16:58.687','2018-01-23 19:27:41.943','admin','admin','testCourtUuid',
'2018-01-01 16:14:30','2027-01-31 16:14:58','74788ee0d70943ceaf2',1,1,1
WHERE
NOT EXISTS (
SELECT 1 FROM acc_device_auth
WHERE user_id = '74788ee0d70943ceaf2' AND delete_flag = 1
);
根据插入数据的user_id和delete_flag判断该条记录是否重复
批量更新时重复改为更新:
INSERT INTO test (id, name, age)
VALUES
('123456', 'test', 1),
('1234567', 'test2', 0)
ON CONFLICT(id)
DO UPDATE
SET name =excluded.name,age=excluded.age
mysql可用:
if not exists(select 1 from tb where 重复列名一='xx' and 重复列名二='xx' and 重复列名三='xx' and 重复列名四='xx')
begin
insert into ...
end
mysql批量插入重复时更新:
<insert id="insertStatFlow" parameterType="java.util.List" >
INSERT INTO house (house_id,community_id,area_code,building_id,building_code,building_name,
unit_id,unit_code,unit_name,house_code,house_name,floor,is_delete)
VALUES
<foreach collection="list" item="item" separator=",">
( #{item.house_id}, #{item.community_id},#{item.area_code},#{item.building_id},
#{item.building_code}, #{item.building_name},#{item.unit_id},#{item.unit_code},
#{item.unit_name}, #{item.house_code},#{item.house_name},#{item.floor},#{item.is_delete})
</foreach>
ON DUPLICATE KEY UPDATE
community_id =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.community_id}
</foreach>
END,
area_code =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.area_code}
</foreach>
END,
building_id =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.building_id}
</foreach>
END,
building_code =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.building_code}
</foreach>
END,
building_name =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.building_name}
</foreach>
END,
unit_id =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.unit_id}
</foreach>
END,
unit_code =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.unit_code}
</foreach>
END,
unit_name =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.unit_name}
</foreach>
END,
house_code =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.house_code}
</foreach>
END,
area_code =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.area_code}
</foreach>
END,
house_name =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.house_name}
</foreach>
END,
floor =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.floor}
</foreach>
END,
is_delete =CASE house_id
<foreach collection="list" item="item" index="index">
WHEN #{item.house_id} THEN #{item.is_delete}
</foreach>
END
</insert>