两个表
A
ID NAME COUNT
1 AA 2
2 BB 4
3 CC 2B
ID NAME COUNT
1 AA 1
3 CC 4如何将A表数据插入到B表里,B表里有NAME相同的COUNT累加,没有的数据插入
得到新的B表
ID NAME COUNT
1 AA 3
2 BB 4
3 CC 6
A
ID NAME COUNT
1 AA 2
2 BB 4
3 CC 2B
ID NAME COUNT
1 AA 1
3 CC 4如何将A表数据插入到B表里,B表里有NAME相同的COUNT累加,没有的数据插入
得到新的B表
ID NAME COUNT
1 AA 3
2 BB 4
3 CC 6
select * from a
ON DUPLICATE KEY UPDATE COUNT=COUNT+ VALUES(COUNT)参考手册中ON DUPLICATE KEY语法。MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
from A left join B on A.name = B.name
select * from a
ON DUPLICATE KEY UPDATE COUNT=COUNT+ VALUES(COUNT)
这个语法不对吧?MYSQL4.0支持这个语法吗?select A.id,A.name,A.count+(case when B.count is null then 0 else B.count end)
from A left join B on A.name = B.name
可以弄出个B表来,但实际B表数据没修改
select a.* from a left join b on a.id=b.id where b.id is nullupdate b inner join a on a.id=b.id set b.count=b.count+a.count
表改名啊?
insert into b
select a.* from a left join b on a.id=b.id where b.id is null
update b inner join a on a.id=b.id set b.count=b.count+a.count
语法不通过哦
select a.* from a left join b on a.id=b.id where b.id is null;
update b inner join a on a.id=b.id set b.count=b.count+a.count;
你不说出来的话,没人能猜出来你用的是MYSQL4.0如果是MYSQL4的话,则你需要用两句SQL来实现这个需求了。
insert into a select * from b where id not in (select id from a);
update();
}else{
insert();
}
并发大的情况,会insert多条