如题:
A、B两个表拥有一样的表结构,都以id为主键,如何将A表中存在而B表中不存在的记录插入到B表中。
表结构如下:
create table A(
id int primary key,
name varchar(20),
id int primary key,
name varchar(20),
password varchar(20)
)
)
create table B(
id int primary key,
name varchar(20),
id int primary key,
name varchar(20),
password varchar(20)
)
)
A表记录:
ID NAME PASSWORD
------------------
1 Tom 1234
2 Mary 1234
3 Lucy 1234
4 Billy 1234
5 Henry 1234
2 Mary 1234
3 Lucy 1234
4 Billy 1234
5 Henry 1234
B表记录:
ID NAME PASSWORD
------------------
1 Tom 1234
2 Mary 1234
3 Lucy 1234
2 Mary 1234
3 Lucy 1234
SQL语句一(通过not in实现):
insert into B select * from A where id not in(select id from B)
Sql语句二(通过not exists实现):
insert into B select * from A a not exists(select * from B b where a.id=b.id)
==================================
若两表的记录不同,如下:
A表记录:
ID NAME PASSWORD
------------------
1 Tom 1234
2 Mary 1234
3 Lucy 1234
4 Billy 2548
5 Henry 1234
2 Mary 1234
3 Lucy 1234
4 Billy 2548
5 Henry 1234
B表记录:
ID NAME PASSWORD
------------------
1 Jojoy 1234
2 Mary 1234
3 Lucy 1234
4 Billy 1234
5 Henry 1234
2 Mary 1234
3 Lucy 1234
4 Billy 1234
5 Henry 1234
找出两表中的不同记录,以下两条SQL语句都能实现。
select * from b where name not in(select name from a) or password not in(select password from a)
select * from b where not exists(select * from a where a.name=b.name and a.password=b.password)