有两个数据库db1和db2,里面表的结构是完全相同的,现在要将db2的表tblOrder合并到db1的tblOrder中,tblOrder中有3个字段:orderid(主键),value1,value2,
合并的规则如下:如果db1的tblOrder中已经存在相同的orderid的记录,则将db2的tblOrder中相应的记录的value1,value2加到db1的tblOrder记录上,如果没有,则执行整条记录插入。求sql语句,谢谢各位~~~~~~~~~~~
合并的规则如下:如果db1的tblOrder中已经存在相同的orderid的记录,则将db2的tblOrder中相应的记录的value1,value2加到db1的tblOrder记录上,如果没有,则执行整条记录插入。求sql语句,谢谢各位~~~~~~~~~~~
insert into db1.tblOrder a select * from db2.tblOrder b on duplicate key update a.value1=a.value1+b.value1 and a.value2=a.value2+b.value2;
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
发现表中除了orderid,value1,value2,还有一个rowid字段,rowid是AUTO_INCREMENT的,而且是主键,之前的orderid是唯一键,合并表的时候可以不管rowid,让它自动就行了,这时候怎么排除rowid?如果select 列出(orderid,value1,value2)的话,又怎么做到像a.value1+b.value1这样的根据a,b取值?
INSERT INTO
UPDATE
谢谢!
但是要知道什么时候该UPDATE麻烦啊!得另外做工作
别名能用到这里吗?版主大大,帮帮忙哈~
select a.* from db2.tblOrder a left join db1.tblOrder b on a.rowid=b.rowid
where b.rowid is nullupdate db1.tblOrder a inner join db2.tblOrder b on a.rowid=b.rowid
set a.a.value1=a.value1+b.value1,a.value2=a.value2+b.value2
我没有说清楚,rowid虽然是主键,但是没有用,两个表都是插入记录时让它自增长的,
应该是on a.orderid=b.orderid,但是怎么排除掉rowid呢?
INSERT INTO B1(F1,F2,...) SELECT F1,F2,.. FROM ....
哦?tblOrder 本来就是unique key。难道直接INSERT INTO * SELECT * FROM ....对AUTO_INCREMENT作为主键的字段即使值相同也是没有主键冲突的?
手册上讲到这里时有一句"AUTO_INCREMENT照常运行",难道就是这个意思?总之,十分感谢各位,晚上试试各条语句~
不过你可以不写个字段insert into aa (co1,col2) select col1,col2 from xx;
或者
insert into aa(id,col1,col2) select null ,col1,col2 from xx
唉,这不又是老问题了?我怎么根据唯一键orderid相同时将value1和value2相加呢?就是说我怎么像1楼那样取到value1和value2呢?a.value1=a.value1+b.value1 ,因为这时候没法写a,b了~~~
select orderid,value1,value2 from db2.tblOrder
on duplicate key
update value1=value1+VALUES(value1) and value2=value2+VALUES(value2);官方手册中有现成的例子。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
按照 insert into db1.tblOrder(orderid,value1,value2)
select orderid,value1,value2 from db2.tblOrder
on duplicate key
update value1=value1+VALUES(value1) and value2=value2+VALUES(value2);
试了,结果是:
ERROR 1052(23000): Column 'value1' in field list is ambiguous很显然三个value1,mysql不知道前两个是db1的,后一个是db2的~~
难道真的没有办法么?
select orderid,value1,value2 from db2.tblOrder
on duplicate key
update db1.tblOrder.value1=db1.tblOrder.value1+VALUES(value1) , db1.tblOrder.value2=db1.tblOrder.value2+VALUES(value2);测试如下mysql> create table csdn.tb1(pd int auto_increment primary key, oid int unique k
ey, v1 int,v2 int);
Query OK, 0 rows affected (0.16 sec)mysql> create table test.tb1(pd int auto_increment primary key, oid int unique k
ey, v1 int,v2 int);
Query OK, 0 rows affected (0.13 sec)mysql> insert into csdn.tb1 values (1,1,10,10);
Query OK, 1 row affected (0.06 sec)mysql> insert into test.tb1 values (1,1,1,1);
Query OK, 1 row affected (0.03 sec)mysql> insert into test.tb1 values (2,2,2,2);
Query OK, 1 row affected (0.02 sec)mysql> select * from csdn.tb1;
+----+------+------+------+
| pd | oid | v1 | v2 |
+----+------+------+------+
| 1 | 1 | 10 | 10 |
+----+------+------+------+
1 row in set (0.00 sec)mysql> select * from test.tb1;
+----+------+------+------+
| pd | oid | v1 | v2 |
+----+------+------+------+
| 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 |
+----+------+------+------+
2 rows in set (0.00 sec)mysql> insert into csdn.tb1(oid,v1,v2)
-> select oid,v1,v2 from test.tb1
-> on duplicate key
-> update csdn.tb1.v1=csdn.tb1.v1+values(v1),csdn.tb1.v2=csdn.tb1.v2+values(v2);
Query OK, 3 rows affected (0.03 sec)
Records: 2 Duplicates: 1 Warnings: 0mysql> select * from csdn.tb1;
+----+------+------+------+
| pd | oid | v1 | v2 |
+----+------+------+------+
| 1 | 1 | 11 | 11 |
| 2 | 2 | 2 | 2 |
+----+------+------+------+
2 rows in set (0.00 sec)mysql>
WWWWA
zhoupuyue
ghost0912