[转帖]update 更新表中多条记录。从另一个表中调用记录。

[quote]
[url=http://blog.sina.com.cn/s/blog_54cd1aa30100oq14.html]update 更新表中多条记录。从另一个表中调用记录。[/url][/quote]
实例效果图:
[img]http://dl.iteye.com/upload/picture/pic/86966/a137d18a-65bb-3521-8284-e9eb55e119ef.jpg[/img]
我的应用：

创建主表：

CREATE TABLE [dbo].[Table_1](
	[id] [int] NOT NULL,
	[num] [numeric](18, 0) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

创建子表：

CREATE TABLE [dbo].[Table_2](
	[id] [int] NOT NULL,
	[num] [numeric](18, 0) NULL,
	[tsp_id] [int] NULL,
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

更新主表NUM的值 子表对应主表ID

update dbo.Table_1 set num+=B.num
 from (select sum(num)as num FROM dbo.Table_2 ) B

[b]原帖COPY:[/b]

update tbA set a3=a3+B.b3
 from (select b1,b3 FROM tbB) B
 where a1 in ( B.b1)

----------------------------------------------

use northwind
GO

create table tem_1( names char(10),number int )
declare @num int
set @num = 1
while @num <10
begin 
insert into tem_1 values ('chen' ,@num)
set @num=@num+1
end

select * from tem_1


create table tem_2( names char(10),number int )
declare @num int
set @num = 10
while @num <20
begin 
insert into tem_2 values ('MAckson' ,@num)
set @num=@num+1
end

select * from tem_2

update tem_1  set names = B.names from tem_1 as A,tem_2 as B where A.number <10 

Drop tem_1

Drop tem_2



Go