急请各位帮忙看看这个sql语句怎么写,谢谢各位在线等.....

[Fibona]

现在有一个表,user_information A 里面有一个字段state 对应于state_dd B里面的stateid,因为开始时state是字符型,有一些数据不合理,要求进行处理,检查看A 表里面的state数据是否合法(存在于B中stateid的算合法).如果不合法,把其值更新为0;
然后将更新后的值插入到表的另一个字段cloumnew中去,请问怎么做,

写了半天了,还是不对,谢谢各位

[Fibona]

谢谢,我去试试回来结贴....

[Fibona]

declare @state varchar(100)

update
user_information a
set
@state = (case
when exists(select 1 from state_dd where stateid = a.state))
then a.state
else '0'
end),
state = @state,
cloumnew = @state
这样有错误,可以再帮忙看看吗?谢谢

[Fibona]

自已改好,谢谢

[wozhuchuanwei]

试试
Update user_information set A.state=0
From user_information A
LEFT JOIN state_dd B ON A.state=B.stateid
where B.stateid Is NULL

[pbsql]

Update user_information
set cloumnew=(case when exists(select * From state_dd
where user_information.state=stateid)
then state else '0' end)

[子陌红尘]

declare @state varchar(100)

update
user_information a
set
@state = (case
when exists(select 1 from state_dd where stateid = a.state)) then state
else '0'
end),
state = @state,
cloumnew = @state

[子陌红尘]

declare @state varchar(100)

update
user_information a
set
@state = (case
when exists(select 1 from state_dd where stateid = a.state))
then a.state
else '0'
end),
state = @state,
cloumnew = @state