--查询 select b.* , a.a as h from b , a where b.id = a.id
create table a(id int, a int) insert into a values(1 , 60 ) insert into a values(2 , 70 ) create table b(id int, b int) insert into b values(1 , 20 ) insert into b values(1 , 30 ) insert into b values(1 , 70 ) insert into b values(2 , 90 ) go--查询 select b.* , a.a as h from b , a where b.id = a.iddrop table a , b/* id b h ----------- ----------- ----------- 1 20 60 1 30 60 1 70 60 2 90 70(所影响的行数为 4 行) */
create table a(id int, a int) insert into a values(1 , 60 ) insert into a values(2 , 70 ) create table b(id int, b int , h int) insert into b values(1 , 20 , null) insert into b values(1 , 30 , null) insert into b values(1 , 70 , null) insert into b values(2 , 90 , null) go--查询 select b.id , b , a.a as h from b , a where b.id = a.id /* id b h ----------- ----------- ----------- 1 20 60 1 30 60 1 70 60 2 90 70(所影响的行数为 4 行) */ --更改,假设B表有列H update b set h = a.a from b ,a where b.id = a.idselect * from b /* id b h ----------- ----------- ----------- 1 20 60 1 30 60 1 70 60 2 90 70(所影响的行数为 4 行) */drop table a , b
1.先修改表b结构: alter table b add h int; go2.然后用表a数据更新表b中列h的数据: update b set h=a.a from a where a.id=b.id; go
select b.* , a.a as h from b , a where b.id = a.id
insert into a values(1 , 60 )
insert into a values(2 , 70 )
create table b(id int, b int)
insert into b values(1 , 20 )
insert into b values(1 , 30 )
insert into b values(1 , 70 )
insert into b values(2 , 90 )
go--查询
select b.* , a.a as h from b , a where b.id = a.iddrop table a , b/*
id b h
----------- ----------- -----------
1 20 60
1 30 60
1 70 60
2 90 70(所影响的行数为 4 行)
*/
insert into a values(1 , 60 )
insert into a values(2 , 70 )
create table b(id int, b int , h int)
insert into b values(1 , 20 , null)
insert into b values(1 , 30 , null)
insert into b values(1 , 70 , null)
insert into b values(2 , 90 , null)
go--查询
select b.id , b , a.a as h from b , a where b.id = a.id
/*
id b h
----------- ----------- -----------
1 20 60
1 30 60
1 70 60
2 90 70(所影响的行数为 4 行)
*/
--更改,假设B表有列H
update b
set h = a.a
from b ,a
where b.id = a.idselect * from b
/*
id b h
----------- ----------- -----------
1 20 60
1 30 60
1 70 60
2 90 70(所影响的行数为 4 行)
*/drop table a , b
alter table b add h int;
go2.然后用表a数据更新表b中列h的数据:
update b set h=a.a
from a
where a.id=b.id;
go