在oracle可以这样用,你看看mysql行不行?insert into table_a(id,value1,value2) (select b.id,b.value1,b.value2 from table_b b where b.value2 not in ( select distinct d.value2 from table_a c,table_b d where c.value2 = d.value2 /* 查处两个表都存在的值 */ ) /* b表中不属于这些值的记录插入到a表中 */ )
是不是与value1也有关联呢?如果是的话这样可以吗: INSERT INTO table_a SELECT table_b.* FROM table_a,table_b WHERE table_b.value2 <> table_a.value2 AND table_a.value1 = table_b.value1;
先把table_a表建立以value1和value2的主键。 然后执行。INSERT INTO table_a SELECT * FROM table_a
我也在寻找这个问题的答案,没结果啊
(select b.id,b.value1,b.value2 from table_b b
where b.value2 not in
(
select distinct d.value2
from table_a c,table_b d
where c.value2 = d.value2 /* 查处两个表都存在的值 */
) /* b表中不属于这些值的记录插入到a表中 */
)
INSERT INTO table_a SELECT table_b.* FROM table_a,table_b WHERE table_b.value2 <> table_a.value2 AND table_a.value1 = table_b.value1;
然后执行。INSERT INTO table_a SELECT * FROM table_a
INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ((expression | DEFAULT),...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=(expression | DEFAULT), ...
[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]如果你在一个有许多条记录行值的 INSERT 中指定关键词 IGNORE,任何在表中现有的 PRIMARY 或 UNIQUE 键上重复的记录行均会被忽略而不被插入。如果你不指定 IGNORE,当有任何记录行在一个现有的键值上重复时,插入均会被中止。
mysql> use test;
Database changed
mysql>
mysql>
mysql> CREATE TABLE `table_a` (
-> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> `value1` VARCHAR (10) NOT NULL,
-> `value2` VARCHAR (10) NOT NULL,
-> PRIMARY KEY(`id`), UNIQUE(`value2`)
-> );
Query OK, 0 rows affected (0.08 sec)mysql>
mysql> insert into table_a (value1,value2) values
-> ('ids','100001'),
-> ('ids82','100002'),
-> ('ids60','100003'),
-> ('ids832','100004'),
-> ('ids','100009');
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql>
mysql> select * from table_a;
+----+--------+--------+
| id | value1 | value2 |
+----+--------+--------+
| 1 | ids | 100001 |
| 2 | ids82 | 100002 |
| 3 | ids60 | 100003 |
| 4 | ids832 | 100004 |
| 5 | ids | 100009 |
+----+--------+--------+
5 rows in set (0.00 sec)mysql>
mysql> CREATE TABLE `table_b` (
-> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> `value1` VARCHAR (10) NOT NULL,
-> `value2` VARCHAR (10) NOT NULL,
-> PRIMARY KEY(`id`), UNIQUE(`value2`)
-> );
Query OK, 0 rows affected (0.15 sec)mysql>
mysql> insert into table_b (value1,value2) values
-> ('ids','100001'),
-> ('ids','100005'),
-> ('ids','100007'),
-> ('ids','100009');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql>
mysql> select * from table_b;
+----+--------+--------+
| id | value1 | value2 |
+----+--------+--------+
| 1 | ids | 100001 |
| 2 | ids | 100005 |
| 3 | ids | 100007 |
| 4 | ids | 100009 |
+----+--------+--------+
4 rows in set (0.00 sec)mysql>
mysql> insert ignore into table_a(value1,value2) select value1,value2 from table_b order by id;
Query OK, 2 rows affected (0.00 sec)
Records: 4 Duplicates: 2 Warnings: 0mysql>
mysql> select * from table_a;
+----+--------+--------+
| id | value1 | value2 |
+----+--------+--------+
| 1 | ids | 100001 |
| 2 | ids82 | 100002 |
| 3 | ids60 | 100003 |
| 4 | ids832 | 100004 |
| 5 | ids | 100009 |
| 6 | ids | 100005 |
| 7 | ids | 100007 |
+----+--------+--------+
7 rows in set (0.00 sec)mysql>