这篇文章主要介绍了mysql中update和select结合使用方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教

方法1：

update
	t_b2b_collect_order a
inner/left/right join(
	select
		income.transin_notice_id as transin_notice_id,
		income.fund_no as fund_no,
		income.bank_card_no as bank_card_no,
		income.check_time as check_time,
		income.audit_opinion as audit_opinion,
		income.refund_time as refund_time,
		income.payer_bank_bic as payer_bank_bic,
		income.channel_material_type as channel_material_type,
		income.is_synonym_collect_flag as is_synonym_collect_flag,
		income.income_id as income_id,
		income.collect_type as collect_type
	from
		t_b2b_collect_order b2b,
		t_gcl_account_income income
	where
		b2b.income_id = income.income_id
		and income.collect_type = 2) c 
on
	a.income_id = c.income_id set
	a.transin_notice_id = c.transin_notice_id,
	a.fund_no = c.fund_no,
	a.payee_bank_account_no = c.bank_card_no,
    a.audit_time = c.check_time,
    a.audit_opinion = c.audit_opinion,
    a.refund_time = c.refund_time,
    a.payer_bank_bic = c.payer_bank_bic,
    a.channel_material_type = c.channel_material_type,
    a.is_synonym_collect_flag = c.is_synonym_collect_flag,
    a.collect_type = c.collect_type;

方法2：

  update
	t_b2b_contract_amount_usage_record
set
	sys_contract_no = (
	select
		sys_contract_no
	from
		t_b2b_contract
	where
		t_b2b_contract.id = contract_id)
where
	sys_contract_no is null;