MySql、SQL Server、Oracle联表进行多字段更新

1、MySql

UPDATE `user` t1
LEFT JOIN tb_user t2 ON t1.id = t2.uid
SET t1.`name` = t2.username
WHERE
	t1.userPhone = 'S-A10022'

2、SQL Server

UPDATE Plate
SET molding_time = NULL,
 send_status = NULL
FROM
	Plate t1
LEFT JOIN [dbo].[WorkOrder] t2 ON t1.work_order_id = t2.id
WHERE
	t2.status = 1
AND (
	t1.send_status IS NULL
	OR t1.send_status = 0
)
AND stowage_count > 0
AND t2.manual_type > 0
AND molding_time IS NOT NULL

UPDATE t1
SET t1.work_shop_type = t2.work_shop_type
FROM
	[PlanProductionDaliy] t1
LEFT JOIN PlanProductionWeek t2 ON t1.week_plan_id = t2.id

3、Oracle

多表多字段

update inf_ta_03 a set (a.fundcode,a.businesscode) =
	(select b.origin_fund_code,v_businesscode from tspdata.fund_ext b
	where b.target_fund_code = a.fundcode )
where exists (select 1 from tspdata.fund_ext b where b.target_fund_code = a.fundcode)
	and a.businesscode = '02';

update tspdata.bank_card_limit t
   set (t.fast_avail, t.fast_frozen, t.order_frozen) =
       (select t.fast_avail + sum(a.fast_avail),
               t.fast_frozen + sum(a.fast_frozen),
               t.order_frozen + sum(a.order_frozen)
          from tspdata.bank_card_limit a
         where a.trans_acctid = t.trans_acctid
         group by a.trans_acctid)
 where t.cash_acctid = '159750';

----在多条数据中取一条更新当前表
update edcdata.cfg_corp_white_list t
set (t.investor_name,t.certificate_type,t.certificate_no) = (
select x.investor_name,x.investor_cert_type,x.investor_cert_no from
(SELECT *  FROM (SELECT ROW_NUMBER () OVER ( PARTITION BY a.fund_acct_code order by a.fund_acct_status) rn, a.* 
      FROM aplus_provider.corp_fund_acct_info a) WHERE rn = 1) x where x.fund_acct_code = t.fund_acct_code
)
where exists (select 1 from aplus_provider.corp_fund_acct_info a where a.fund_acct_code = t.fund_acct_code);

merge是先做on的判断，最后做update和insert，on的时候没有数据就做insert，insert时候的条件not exists也是在insert前统一做判断，最后批量insert

merge into
     aplus_provider.corp_trade_confirm t
     using edcdata.cust_trade_detail_info t1
     on  t.ta_serial_no = t1.ta_serial_no
when matched then
  update set t.confirm_share = t1.confirm_share
when not matched then
  insert values     
  (to_date('30-06-2023', 'dd-mm-yyyy'), t1.ta_serial_no, to_date('30-06-2023', 'dd-mm-yyyy'), '2.7310122E7', '70', 'B884317424', 'B884317424', '656', '54668', '512690', 'A', 0.0000, 1526900.0000, 0.00, 1526900.0000, 0.0000, null, null, null, null, 0.76230000, null, null, null, null, null, null, null, null, null, null, null, null, null, '1', '0000', 'E')
  where not exists (select 1 from aplus_provider.corp_trade_confirm c where c.ta_serial_no = t1.ta_serial_no);

merge into tspdata.bank_card_share_fund t
using (select sum(t.frozen_share) frozen_share,
           t.cash_acctid,
           t.trans_acctid,
           t.sub_trans_acctid,
           t.portfolio_code,
           t.fund_code
      from tspdata.bank_card_share_change t
     where t.trans_acctid = '68000220824'
       and t.portfolio_code = 'PF0005'
       and t.change_type in ('71', '75')
	   group by t.cash_acctid,
           t.trans_acctid,
           t.sub_trans_acctid,
           t.portfolio_code,
           t.fund_code
	   ) t1
on (t.cash_acctid = t1.cash_acctid
         and t.trans_acctid = t1.trans_acctid
         and t.sub_trans_acctid = t1.sub_trans_acctid
         and t.portfolio_code = t1.portfolio_code
         and t.fund_code = t1.fund_code)
when matched then 
update set t.frozen_share = t1.frozen_share;

merge into tspdata.cif_corp_info t
using (select
t2.cust_no,
t1.CONTROLLING_ID_CODE,
t1.CONTROLLING_ID_TYPE,
t1.CONTROLLING_ID_VALID_DATE,
t1.INDUSTRY_CODE,
t1.CURRENCY,
t1.MANAGE_ADDRESS,
t1.IS_SOCIAL_UNIFY_CREDIT_CODE,
t1.ACTUAL_HOLDER,
t1.ACTUAL_HOLDER_ID_TYPE,
t1.ACTUAL_HOLDER_ID_CODE,
t1.ACTUAL_HOLDER_ID_VALID_DATE,
t1.TAX_ENROL,
t1.CONTROLLING_OWNER_REMARK,
t1.ACTUAL_HOLDER_REMARK,
t1.SZACCO,
t1.SHACCO,
t1.OPEN_TA_ACCT_TYPE,
t1.IS_YANGLAO,
t1.INST_FAX,
t1.POSTCD,
t1.PROFESSIONAL_TYPE,
t1.BIRTH_DAY
from tspdata.cif_corp_info t
left join tspdata.acct_trans t2 on t.CUST_NO = t2.CUST_NO
left join (SELECT *  FROM (SELECT ROW_NUMBER () OVER ( PARTITION BY t.trans_acctid ORDER BY t.CREATE_DATE DESC ) rn, t.* FROM bosdata.acct_apply t where t.acct_opt_type = '3' and t.STATE = '3') WHERE rn = 1) t1 on t2.trans_acctid = t1.trans_acctid
where t1.appv_date > '20210308000000'
and (t.CONTROLLING_ID_CODE != t1.CONTROLLING_ID_CODE
or t.CONTROLLING_ID_TYPE != t1.CONTROLLING_ID_TYPE
or t.CONTROLLING_ID_VALID_DATE != t1.CONTROLLING_ID_VALID_DATE
or t.INDUSTRY_CODE != t1.INDUSTRY_CODE
or t.REGISTERED_CURRENCY != t1.CURRENCY
or t.MANAGE_ADDRESS != t1.MANAGE_ADDRESS
or t.IS_SOCIAL_UNIFY_CREDIT_CODE != t1.IS_SOCIAL_UNIFY_CREDIT_CODE
or t.ACTUAL_HOLDER != t1.ACTUAL_HOLDER
or t.ACTUAL_HOLDER_ID_TYPE != t1.ACTUAL_HOLDER_ID_TYPE
or t.ACTUAL_HOLDER_ID_CODE != t1.ACTUAL_HOLDER_ID_CODE
or t.ACTUAL_HOLDER_ID_VALID_DATE != t1.ACTUAL_HOLDER_ID_VALID_DATE
or t.TAX_ENROL != t1.TAX_ENROL
or t.CONTROLLING_OWNER_REMARK != t1.CONTROLLING_OWNER_REMARK
or t.ACTUAL_HOLDER_REMARK != t1.ACTUAL_HOLDER_REMARK
or t.SZACCO != t1.SZACCO
or t.SHACCO != t1.SHACCO
or t.TA_ACCT_TYPE != t1.OPEN_TA_ACCT_TYPE
or t.IS_YANGLAO != t1.IS_YANGLAO
or t.FAX != t1.INST_FAX
or t.POSTCD != t1.POSTCD
or t.PROFESSIONAL_TYPE != t1.PROFESSIONAL_TYPE
or t.BIRTH_DAY != t1.BIRTH_DAY)) t1
on (t.cust_no = t1.cust_no)
when matched then 
update set t.CONTROLLING_ID_CODE = t1.CONTROLLING_ID_CODE,
t.CONTROLLING_ID_TYPE = t1.CONTROLLING_ID_TYPE,
t.CONTROLLING_ID_VALID_DATE = t1.CONTROLLING_ID_VALID_DATE,
t.INDUSTRY_CODE = t1.INDUSTRY_CODE,
t.REGISTERED_CURRENCY = t1.CURRENCY,
t.MANAGE_ADDRESS = t1.MANAGE_ADDRESS,
t.IS_SOCIAL_UNIFY_CREDIT_CODE = t1.IS_SOCIAL_UNIFY_CREDIT_CODE,
t.ACTUAL_HOLDER = t1.ACTUAL_HOLDER,
t.ACTUAL_HOLDER_ID_TYPE = t1.ACTUAL_HOLDER_ID_TYPE,
t.ACTUAL_HOLDER_ID_CODE = t1.ACTUAL_HOLDER_ID_CODE,
t.ACTUAL_HOLDER_ID_VALID_DATE = t1.ACTUAL_HOLDER_ID_VALID_DATE,
t.TAX_ENROL = t1.TAX_ENROL,
t.CONTROLLING_OWNER_REMARK = t1.CONTROLLING_OWNER_REMARK,
t.ACTUAL_HOLDER_REMARK = t1.ACTUAL_HOLDER_REMARK,
t.SZACCO = t1.SZACCO,
t.SHACCO = t1.SHACCO,
t.TA_ACCT_TYPE = t1.OPEN_TA_ACCT_TYPE,
t.IS_YANGLAO = t1.IS_YANGLAO,
t.INST_FAX = t1.INST_FAX,
t.FAX = t1.INST_FAX,
t.POSTCD = t1.POSTCD,
t.PROFESSIONAL_TYPE = t1.PROFESSIONAL_TYPE,
t.BIRTH_DAY = t1.BIRTH_DAY;

merge into tspdata.bank_card_limit_daily t
using tspdata.bank_card_limit_daily t1  ---以这个表为基准，on条件满足的则matched，不满足则not
on (t.cash_acctid = t1.cash_acctid
   and t.trans_acctid = t1.trans_acctid
   and t.settle_date = '20230117'
   and t1.settle_date = '20230116')
---匹配上就更新
when matched then 
  update set t.retn_share = t1.retn_share,t.update_date = sysdate,t.update_by = 'update_retn'
  where t.retn_share != t1.retn_share  ---只更新不一样的数据
---没匹配上则新增
when not matched then
  insert values
    (t1.cash_acctid, t1.trans_acctid, t1.fund_code,'20230117', t1.end_day_share, t1.income_share, 0.00, 0.00, 0.00, 0.00, 0.00, '1', '123', sysdate, '123', sysdate,t1.retn_share,0.00)
  where t1.settle_date = '20230116';---记得加条件 不然就会将t1表中所有没匹配上的做新增，或者将t1表做子查询缩小匹配的范围

单字段

update tspdata.bank_card_limit_daily a1 set 
                        a1.end_day_share = (select b.end_day_share from tspdata.bank_card_limit b where b.cash_acctid = a1.cash_acctid),
                        a1.update_by = '123---',
                        a1.update_date = sysdate
                      where a1.cash_acctid = '68107916' and a1.settle_date = '20210728';