请教一个sql语句的写法,关于用本表的字段值更新本表的字段

Question

请教一句sql语句: tableA序号   值---------1     AA2     BB3     CC4     25     1--------我想变成的结果是下面这样子,也就是说4和5根据值字段的数去拿序号1和2的值来update自己,这个update语句要怎么写呢? tableA序号   值---------1     AA2     BB3     CC4     BB5     AA

Answer 1

自已看吧，我写了个示例

我已经测试成功了，关键点就是自连接

drop table tb

create table tb (
序号 int,
值 varchar(10)
)

insert tb
select 1,'AA' union all
select 2,'BB' union all
select 3,'CC' union all
select 4,'2' union all
select 5,'1'


select *
from tb


update a
set a.值=b.值
from tb a left join tb b on (a.值=rtrim(cast(b.序号 as varchar)))
where b.序号 is not null

select *
from tb

Answer 2

select  tablea.序号，t.值  from  tablea left join tablea t on tablea.序号=t.值

Answer 3

是update哦

Answer 4

这个也行

Answer 5

序号：ID，值：NAME

用的是sqlite数据库，没有top 1

update tableA set NAME=

(select bNAME from (select a.ID as aID, b.NAME as bNAME from tableA a left join tableA b on a.NAME=b.ID where b.NAME<>'' ) 

where aID = ID)where ID in (select a.ID as aID from tableA a left join tableA b on a.NAME=b.ID where b.NAME<>'' )

Answer 6

这个比较复杂