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

引用

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

实例效果图:

我的应用：

创建主表：

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

原帖COPY:

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