实现一条sql语句修改两条记录
项目过程中遇到了修改默认地址的问题,一个用户只能有一个默认地址。
- 实现方式一
- 将原来的默认地址设置为普通地址
update address set type = 0 where type = 1 and uid = #{aid};
- 修改要替换的地址为默认地址
使用这种方式需要两条SQL语句,建立两次连接,这种方式会有第一条执行完,第二条由于某种原因(例如:宕机)而不能正常执行的情况,这样就不满足需求,就会产生用户没有默认地址的情况。update address set type = 1 where aid = #{aid} and uid = #{uid};
- 实现方式二
使用方式二则可以一步实现update mi_address as addr3, mi_address as addr4 set addr3.type= 1, addr4.type = 0 where addr3.uid = #{uid} and addr3.aid = #{aid} and addr4.uid = #{uid} and addr4.aid = (select addr2.aid from (select addr1.aid, addr1.type, addr1.uid from mi_address as addr1 where addr1.uid = #{uid}) addr2 where addr2.type = 1 and addr2.uid = #{uid});