sql 数据迁移a表往b表迁移，有外键关联

-- 该句实现了一些特定业务，用到了sql函数Right()、INSTR()
SELECT count(*) FROM sys_region AS a INNER JOIN cpcp_region AS b ON INSTR(a.merger_name,b.region_name)>0 AND RIGHT(a.merger_name,CHAR_LENGTH(b.region_name))=b.region_name;
 
-- 实现了将多表查询结果进行批量更新数据操作
update sys_region a 
inner join cpcp_region b on INSTR(a.merger_name,b.region_name)>0 AND RIGHT(a.merger_name,CHAR_LENGTH(b.region_name))=b.region_name
set a.region_code = b.region_id
 

MySQL数据库涉及到多表更新方法

方法一

通过子查询关联

UPDATE tableA a set a.b_rel_field = (SELECT b.id from tableB b where a.name = b.name);

方法二

使用逗号操作符的内连接

UPDATE tableA a, tableB b SET a.price=b.price WHERE a.id = b.id;

方法三

使用SELECT语句中允许的任何类型的联接，比如内连接，左连接

update tableA a inner join tableB b on a.b_rel_field = b.id set a.fieldA = b.fieldA, a.fieldB = b.fieldB;

参考地址