求一UPDATE 语句

[G66606495]

表A 字段 A1、A2、A3
1 2 3
1 4 8
2 5 3
2 6 8
。。。。。。。。。。。。。。
修改为
1 2 3
1 2 8
2 5 3
2 5 8
。。。。。。。。。。。。。。
A 表中A1字段相等两条记录 把A3等于8的那一条的A2改为A3 等于3的A2 值

[mugua604]

A2的值是取最小的吗？还是按什么规则来取呢？

[chuifengde]

update [Table] set A2=(select A2 from [Table] where A1=a.A1 where A3=3) from [Table] a where A3=8

[G66606495]

A 表中一定有A1字段相等的两条记录 把A3等于8的那一条的A2值改为A3 等于3的A2 值

[mugua604]

DECLARE @t TABLE (A1 VARCHAR(2),A2 VARCHAR(2),A3 VARCHAR(3))
INSERT @t SELECT '1','2','3'
UNION ALL SELECT '1','4','8'
UNION ALL SELECT '2','5','3'
UNION ALL SELECT '2','6','8'


SELECT * FROM @t

update @t set A2=(select A2 from @t where A1=a.A1 and A3=3) from @t a where A3=8

select * from @t

======
(4 行受影响)
A1 A2 A3
---- ---- ----
1 2 3
1 4 8
2 5 3
2 6 8

(4 行受影响)

(2 行受影响)

A1 A2 A3
---- ---- ----
1 2 3
1 2 8
2 5 3
2 5 8

(4 行受影响)

[水族杰纶]

declare @t table(A1 int ,A2 int ,A3 int)
insert @t select 1, 2 , 3
insert @t select 1 ,4, 8
insert @t select 2 ,5 , 3
insert @t select 2 ,6 , 8
update a set A2=(select top 1 A2 from @t where A1=a.A1 and A3=3)from @t a where a.A3=8
select * from @t
(2 行受影响)
A1 A2 A3
----------- ----------- -----------
1 2 3
1 2 8
2 5 3
2 5 8

[cl9132008]

[Quote=引用 2 楼 chuifengde 的回复:]
SQL codeupdate [Table] set A2=(select A2 from [Table] where A1=a.A1 where A3=3) from [Table] a where A3=8
[/Quote]
这种简便的方法，其实没那么复杂

[Liyingyue_FFS]

update a set a2=(select A2 from a where A1=dbo.a.A1 and A3=3) from dbo.a where A3=8 

[日月小小]

如下:
update A set A2=(select A2 from A where A3=3 and A1=1)where A3=8 and A1=1

[yunmoon]

学习

[LiloZhu]

declare @t1 table(A1 varchar(4),
		 A2 varchar(4),
		 A3 varchar(4)
		 )


insert into @t1 
select 	        '1', '2','3' 
union select    '1', '4','8' 
union select    '2', '5','3'
union select    '2', '6','8' 


--select * from @t1

update b set b.a2 =(select min(a2) from @t1 where b.a1 = a1  ) from @t1 b

select * from @t1

---Result--
A1   A2   A3   
---- ---- ---- 
1    2    3
1    2    8
2    5    3
2    5    8

[huangqing_80]

楼上的各位，好象缺了一条判断A1相等的两条记录的条件吧

[LiloZhu]

declare @t1 table(A1 varchar(4),
		 A2 varchar(4),
		 A3 varchar(4)
		 )


insert into @t1 
select 	        '1', '2','3' 
union select    '1', '4','8' 
union select    '2', '5','3'
union select    '2', '6','8' 


--select * from @t1

/*A1 相同，取最小的A2 的值*/

--update b set b.a2 =(select min(a2) from @t1 where b.a1 = a1  ) from @t1 b

--select * from @t1

/*A 表中A1字段相等两条记录 把A3等于8的那一条的A2改为A3 等于3的A2 值 */

update b set b.a2=(select a2 from @t1 where b.a1 = a1 and A3 ='3' ) from @t1 b where b.a3 = '8'

select * from @t1


--Result--

A1   A2   A3   
---- ---- ---- 
1    2    3
1    2    8
2    5    3
2    5    8