mysql 批量从一个表更新另一个表

背景

新加字段需要依赖表中的一个字段 批量处理
批量删除某个表的字段或者另外一个表

查阅

可以使用inner join

具体案例

更新

UPDATE cloud_instances
INNER JOIN cloud_domain_extras ON cloud_domain_extras.domain_id = cloud_instances.instance_name 
SET cloud_instances.auto_renew = cloud_domain_extras.auto_renew,
cloud_instances.amortization_subject_id = cloud_domain_extras.amortization_subject_id,
cloud_instances.expense = cloud_domain_extras.expense,
cloud_instances.expense_unit = cloud_domain_extras.expense_unit,
cloud_instances.user_id = cloud_domain_extras.user_id

删除

	DELETE dashboard_instance_monitor 
FROM
  dashboard_instance_monitor
  LEFT JOIN cloud_account ON dashboard_instance_monitor.cloud_account_id = cloud_account.id 
WHERE
  cloud_account.account_status = "自治管理"; 

更新本表字段

UPDATE cloud_financial_bills set billing_day = ( SELECT case when LENGTH(usage_start_time) > 0 then  left( usage_start_time,10) else NULL end)