根据同一张表更新同一张表字段内容sql

[scounix2003]

表a的某些字段更新：
字段 1 acno
字段 2 acname
现在a的记录有些acname是空的，有的有，现在想把同一个acno的acname空的填写好，怎么写sql啊？
如：
12345 abcde
12345
12345 abcde
12344 bcdef


现在我想把第二条的acname也修改成abcde，怎么写sql啊？

[开着拖拉机泡妞]

update 表A set acname=a.acname from 表A a where a.acno=表A.acno

[scounix2003]

1> update abis_flow set acname = a.acname from abis_flow a where a.acno=abis_flow.acno
2> go
Msg 107, Level 15, State 1:
Line 2:
The column prefix 'abis_flow' does not match with a table name or alias name
used in the query. Either the table is not specified in the FROM clause or it
has a correlation name which must be used instead.
1>

[scounix2003]

[Quote=引用 2 楼 的回复:]

1> update abis_flow set acname = a.acname from abis_flow a where a.acno=abis_flow.acno
2> go
Msg 107, Level 15, State 1:
Line 2:
The column prefix 'abis_flow' does not match with a table name or ……
[/Quote]
不行，报错啦

[开着拖拉机泡妞]

思路就是这样，自己改一下。没环境，没办法

[dawugui]

create table a(acno varchar(10),acname varchar(10))
insert into a values('12345', 'abcde')
insert into a values('12345', null   )
insert into a values('12345', 'abcde')
insert into a values('12344', 'bcdef')
go

update a set acname = (select top 1 acname from a where acno = t.acno and acname <> '' and acname is not null) from a t where acname = '' or acname is null

select * from a
/*
acno       acname     
---------- ---------- 
12345      abcde
12345      abcde
12345      abcde
12344      bcdef

（所影响的行数为 4 行）
*/

drop table a 

[D_B_H]

update a set acname =
(
select cl from
(
select acno,ISNULL(MIN(acname),'NUll') cl from a
group by acno
) as t
where a.acname = t.acno
)

[yuanmeixiang]

update tb set acon = select max(t1.acon) from tb t1 where tb.acon = t1.acno where tb.acname is null

[yuanmeixiang]

update t1 set t1.value = (select top 1 value from t1 a where a.类型名称= 类型名称) where value is null

select * from t1

[jyh070207]

update table_name
set acname = (select top 1 from table_name t where t.acno = table_name.acno and t.acname is not null)
where acname is null

[jyh070207]

前面一个有错

update table_name
set acname = (select top 1 t.acname from table_name t where t.acno = table_name.acno and t.acname is not null)
where acname is null

[yuanmeixiang]

update a set acname = (select max(t1.acno) from a t1 where a.acno = t1.acno)
where a.acname is null