项目里遇到要把一张表到字段复制到另一张表,但是有一个特殊的地方是这两张表的主键都是uuid,而且两张表需要复制过去的字段的字段名都相同的。
首先来介绍一下这两张表的情况:
表A:
id B_id 字段1 字段2 字段3
表B:
id 字段1 字段2 字段3
其中表B的id对应表A的B_id,表A的id为uuid
针对这个情况,进行多次尝试,首先是第一次尝试:
insert into 表A (
字段1,
字段2,
字段3
)
select
字段1,
字段2,
字段3,
form 表B;
第一次尝试直接报错:
Field 'id' doesn't have a default value
百度了一下之后,网上一片说主键要自增什么的,但是我的表A主键是UUID,肯定不适用,所以进行了第二次尝试:
insert into 表A (
字段1,
字段2,
字段3
)
select
字段1,
字段2,
字段3,
form 表B
left join 表A on 表B.id = 表A.B_id;
第二次尝试直接报错:
Colum '字段1' in field list is ambigous
百度了一下,这个问题是因为两张表的字段相同不能join,这里注意一下,这里进行左连接很明显是一个错误的写法,达不到目的,这个等一下讲,但是当时我还是硬非要先把这个报错处理一下看看什么原因。处理上面这个报错很简单,每个字段前面加上表名就可以了:
insert into 表A (
表A.字段1,
表A.字段2,
表A.字段3
)
select
表B.字段1,
表B.字段2,
表B.字段3,
form 表B
left join 表A on 表B.id = 表A.B_id;
第三次运行直接报错:
Field 'id' doesn't have a default value
好家伙一个循环。这里先将一下这个尝试错在哪里,这个sql的后半段,左连接得到的结果是根据表B.id = 表A.B_id
得到了,但是得到的结果是没法和前半段sql中对应上的,因为问题还是一开始的问题,没有对应的主键。
紧接着进行了第四次尝试:
update 表A
set
表A.字段1=表B.字段1,
表A.字段2=表B.字段2,
表A.字段3=表B.字段3
where 表B.id = 表A.B_id;
第四次尝试直接报错:
语法错误:
where 表B.id = 表A.B_id
这里的错误我理解是where后面跟上了两张表的条件,应该只能包含当前表:
下面这部分是我从网上找到的:
update set from 语句格式
当where和set都需要关联一个表进行查询时,整个 update执行时,就需要对被关联的表进行两次扫描,显然效率比较低。
对于这种情况,Sybase和SQL SERVER的解决办法是使用UPDATE…SET…FROM…WHERE…的语法,实际上就是从源表获取更新数据。在 SQL 中,表连接(left join、right join、inner join 等)常常用于 select 语句,其实在 SQL 语法中,这些连接也是可以用于update 和 delete 语句的,在这些语句中使用 join 还常常得到事半功倍的效果。
Update 表A SET 表A.字段1 = 表B.字段1
FROM 表A A LEFT JOIN 表B B ON B.id=A.B_id用来同步两个表的数据!
但是!并不适用,无论使用什么写法,都会在from行报错,或者使用where替换from之后在where行报错!
不可行:
update 表A set 表A.字段1=表B.字段1 where 表A.B_id = 表B.id
报错:
unkonwn colum '表B.id' in where clause
不可行:
update 表A set 表A.字段1=表B.字段1 from 表A where 表A.B_id = 表B.id
不可行:
update 表A set 表A.字段1=表B.字段1 from 表A,表B where 表A.B_id = 表B.id
报错:
from行语法错误
筋疲力尽之下,mysql应该不支持上面的语法,最后还是祭出杀手锏:
update 表A
JOIN 表B
on 表A.B_id = 表B.id
SET
表A.字段1=表B.字段1,
表A.字段2=表B.字段2,
表A.字段3=表B.字段3
OK,没问题测试通过。
或许
update 表A JOIN 表B ON 表A.id = 表B.id SET 表A.字段1=表B.字段1
才是符合mysql语法的将将一张表的部分字段复制到另一张表的方法把!