关于update联表更新数据Mysql和Oracle语法

Mysql
UPDATE info1 t1 left JOIN info2 t2 
ON t1.name = t2.name
SET t1.age = t2.age, t1.class = t2.class;

或者

UPDATE info1 t1 ,info2 t2 
SET t1.age = t2.age, t1.class = t2.class

where t1.name = t2.name

或者

  update t_cus_year_status t
      left join t_customer_infos cus on cus.id_ = t.cus_id_
      set t.charger_id_  =6
      where......;

mysql的更新与oracle不同，是必须要先join on来进行联表，然后才set的，不然就会报语法错误

Oracle

UPDATE BDA_SZ_T_F_DWFX_ZB A  set A.zbvalue = (SELECT B.zbvalue FROM BDA_SZ_T_F_DWFX_ZB_bak B where  A.RQ = B.RQ
AND A.DQ = B.DQ 
AND A.ZBGUID = B.ZBGUID )

where

exist  (SELECT B.zbvalue FROM BDA_SZ_T_F_DWFX_ZB_bak B where  A.RQ = B.RQ
AND A.DQ = B.DQ 
AND A.ZBGUID = B.ZBGUID )

考虑是否加exist  不加的话关联不上的会被赋值为null

或者

update (SELECT * FROM BDA_ZS_T_SYS_YD_YDK YDK LEFT JOIN BDA_ZS_T_SYS_YD_RW_YD YD ON YDK.YD_CODE = YD.YD_CODE WHERE YD.YD_CODE IS NULL AND YDZT = '0') YDK set ydzt = '0'

update中使用case  when

 update BDP_V_BOND_MAP_WZCSHLD set lvl = (CASE length(AD_CODE) WHEN 2 THEN 2  WHEN 4 THEN 4  else 6 END)