目的:
将db2.tb2中的最新数据更新到db1.tb1
两表以name字段做关联
db2.tb2只是记录表,name有大量重复
db1.tb1中的数据是最新,且name不能重复
db1.tb1
----------------------
id,name,age,count,time
----------------------
1,abc,1,40,2010-09-10 10:20
2,pig,2,50,2010-09-11 10:20
3,dog,3,60,2010-09-12 10:20
db2.tb2
-----------------------
id,name,age,count,time
-----------------------
1,abc,11,220,2010-09-09 05:20
2,abc,21,320,2010-09-13 07:20
3,abc,31,420,2010-09-08 11:20
4,pig,41,520,2010-09-10 09:20
5,pig,51,620,2010-09-13 06:20
6,dog,61,720,2010-09-15 03:20
7,dog,71,820,2010-07-10 12:20
结果需要是这样:
db1.tb1
----------------------
id,name,age,count,time
----------------------
1,abc,21,320,2010-09-13 07:20
2,pig,51,620,2010-09-13 06:20
3,dog,61,720,2010-09-15 03:20
将db2.tb2中的最新数据更新到db1.tb1
两表以name字段做关联
db2.tb2只是记录表,name有大量重复
db1.tb1中的数据是最新,且name不能重复
db1.tb1
----------------------
id,name,age,count,time
----------------------
1,abc,1,40,2010-09-10 10:20
2,pig,2,50,2010-09-11 10:20
3,dog,3,60,2010-09-12 10:20
db2.tb2
-----------------------
id,name,age,count,time
-----------------------
1,abc,11,220,2010-09-09 05:20
2,abc,21,320,2010-09-13 07:20
3,abc,31,420,2010-09-08 11:20
4,pig,41,520,2010-09-10 09:20
5,pig,51,620,2010-09-13 06:20
6,dog,61,720,2010-09-15 03:20
7,dog,71,820,2010-07-10 12:20
结果需要是这样:
db1.tb1
----------------------
id,name,age,count,time
----------------------
1,abc,21,320,2010-09-13 07:20
2,pig,51,620,2010-09-13 06:20
3,dog,61,720,2010-09-15 03:20
on a.name=b.name
set a.age=c.age,a.count=c.count,a.time=c.time
ts2 建立触发器 insert时插入 tb1
select a.* from db2 a inner join (
select name,max(time) as ma from db2 group by name) b
on a.name=b.name and a.time=b.ma) b1 on a.name=b1.name
set a1.age=b1.age,a1.count=b1.count,a1.time=b1.time
set a.age=k.age,a.count=k.count,a.time=k.time
where a.name=k.name ;
select a.* from db2 a inner join (
select name,max(time) as ma from db2 group by name) b
on a.name=b.name and a.time=b.ma) b1 on a1.name=b1.name
set a1.age=b1.age,a1.count=b1.count,a1.time=b1.time
Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='
show variables like 'char%';