mysql 将字段复制到另一个字段（数据运维）

1.将同一个表中的一个字段的值复制给另一个字段

UPDATE t_user
SET signed_time = create_time   

create_time   是源数据，signed_time是目标数据，之前是空的

2.将同一个表中两个类型一样的字段的值互换
UPDATE t_user u1, t_user u2
SET u1.signed_time = u2.create_time,
u2.create_time = u1.signed_time

3.不同表一列复制到另一个列
update table1,table2 set table1.field1=table2.field2 where table1.id=table2.id

table2.field2 是源数据，table1.field1是空的

实例

UPDATE srm_order_info a ,v_t001k_assign_sap b set a.company = b.BUTXT,a.update_time = NOW() WHERE a.company_code = b.BUKRS and (a.company is null  or a.company = "")

 使用inner join方式联表更新

UPDATE srm_warehousing_entry a inner join sheet10 b on a.material_vouche = b.material_vouche and a.zeile = b.zeile
SET a.lfbnr = b.lfbnr, a.lfpos = b.lfpos 
WHERE a.material_vouche = b.material_vouche and a.zeile = b.zeile;

 

 需要联表例子

update nyx_work_ext LEFT JOIN yx_work on nyx_work_ext.work_id = yx_work.work_id,dwd_erp_online_project_df set nyx_work_ext.relate_id=dwd_erp_online_project_df.relate_id where yx_work.erp_work_id 


=dwd_erp_online_project_df.id

 

 

 

4.复制旧表数据到新表

insert into 新表(字段1,字段2,…….) select 字段1,字段2,…… from 旧表

 

下面这个是旧表联表的情况

insert into nyx_work_ext(work_id,work_type,work_code,work_status,work_categary,work_desc,expected_income,pre_cost,pre_expense,
expected_sign_time,pre_gross_profit,gross_profit_rate,expected_year_income,pre_year_cost,
year_gross_profit_rate,
link_work,plan_finished_time,work_domanial,work_amount


) select a.work_id , b.work_type,b.work_code,b.work_status,b.work_categary,
b.work_desc,b.expected_income,b.pre_cost,b.pre_expense,b.expected_sign_time,b.pre_gross_profit,
b.gross_profit_rate,null,null,null,b.link_work,b.expect_end_time,b.industry_sector,null
from
yx_work a LEFT JOIN dwd_erp_online_project_df b on a.erp_work_id = b.id

 

 

 

注意更新时加上where条件，避免过多更新
————————————————
原文链接：https://blog.csdn.net/qq_33240556/article/details/123425050