假设有一原始Table与预期Table:原始Table 预期Table
Name Type Value Name Type Value
A 1 0 A 1 0
A 2 10 A 2 20
A 3 20 A 3 20
A 4 30 A 4 30
B 1 0 B 1 0
B 2 10 B 2 20
B 3 20 B 3 20
B 4 30 B 4 30
C 1 0 C 1 0
C 2 10 C 2 20
C 3 20 C 3 20
C 4 30 C 4 30要如何把每个Type= 2那一列Record的Value更改为跟Type=3那一列Record的Value一样?
Name Type Value Name Type Value
A 1 0 A 1 0
A 2 10 A 2 20
A 3 20 A 3 20
A 4 30 A 4 30
B 1 0 B 1 0
B 2 10 B 2 20
B 3 20 B 3 20
B 4 30 B 4 30
C 1 0 C 1 0
C 2 10 C 2 20
C 3 20 C 3 20
C 4 30 C 4 30要如何把每个Type= 2那一列Record的Value更改为跟Type=3那一列Record的Value一样?
from tb a where Type=2
declare @table table ([Name] varchar(1),[Type] int,[Value] int)
insert into @table
select 'A',1,0 union all
select 'A',2,10 union all
select 'A',3,20 union all
select 'A',4,30 union all
select 'B',1,0 union all
select 'B',2,10 union all
select 'B',3,20 union all
select 'B',4,30 union all
select 'C',1,0 union all
select 'C',2,10 union all
select 'C',3,20 union all
select 'C',4,30update @table set [Value]=
(select [Value] from @table where [Name]=a.[Name] and [Type]=3)
from @table a where [Type]=2select * from @table
/*
Name Type Value
---- ----------- -----------
A 1 0
A 2 20
A 3 20
A 4 30
B 1 0
B 2 20
B 3 20
B 4 30
C 1 0
C 2 20
C 3 20
C 4 30
*/
insert into tb select 'A',1,0
insert into tb select 'A',2,10
insert into tb select 'A',3,20
insert into tb select 'A',4,30
insert into tb select 'B',1,0
insert into tb select 'B',2,10
insert into tb select 'B',3,20
insert into tb select 'B',4,30
insert into tb select 'C',1,0
insert into tb select 'C',2,10
insert into tb select 'C',3,20
insert into tb select 'C',4,30
go
update a set a.Value=b.Value
from tb a inner join tb b on a.name=b.name and a.type=b.type-1
where a.type=2
select * from tb
/*
Name Type Value
---------- ----------- -----------
A 1 0
A 2 20
A 3 20
A 4 30
B 1 0
B 2 20
B 3 20
B 4 30
C 1 0
C 2 20
C 3 20
C 4 30(12 行受影响)*/
go
drop table tb