[db] update

[Alice52]

1. 级联更新数据: 保证冗余数据的一致

   explain update all_star_judgement jud, all_star_position_sign_up psu
   set jud.store_id = psu.store_id, jud.position_id = psu.position_id
   where jud.sign_up_id = psu.id   and psu.id <= 50000

   UPDATE table_1 t1
   SET foo = 'new_value'
   FROM table_2 t2
      JOIN table_3 t3 ON t3.id = t2.t3_id
   WHERE
      t2.id = t1.t2_id
      AND t3.bar = True;

2. 一表批量

   update order
   set order_status=
        case user_id when 'jack' then ‘待收货’
                            when ‘tom’ then '已收货'
        end
   where user_id in ('jack','tom')

3. in 的 update 尽量优化成 join 做

   update dbo.MCD_WeiXin_TagMemberInfo set Flag=3
   where TagId=@bill_tagId and UserAccount not in (
     select EmployeeNo from MCD_WeiXin_MCOrgAndUserSyncInfo
     where PersonType in ('雇员','雇员.非申请人','雇员.申请人')
           and JobFamily = 'Staff' and EMPLOYER=@bill_tagname         )

   WITH latest_store as (
      SELECT
          sj.id, am.store_id
      FROM smt_judgement sj
      LEFT JOIN account_member am on am.id = sj.member_id
      LEFT JOIN mcd_store ms on ms.id = am.store_id
      WHERE sj.is_deleted=0 AND sj.store_id != am.store_id
          AND am.is_deleted=0 AND am.is_active=1
          AND ms.is_deleted=0 AND ms.store_ops_status='A'
   )
   
   UPDATE smt_judgement sj, latest_store latest
   SET sj.store_id=latest.store_id
   WHERE sj.id=latest.id

---

reference

1. 多表更新
2. pgsql

[Alice52]

upsert

1. 功能
2. 多key
3. 死锁

---

reference

1. https://blog.csdn.net/Dkangel/article/details/121124065
2. https://time.geekbang.org/column/article/75173