update b
set Account = case when len(a.Account)<6
then a.ID+(REPLICATE('0', 6-len(a.Account))+a.Account))
else a.Account End
from a,b,c
where a.OldAccout = b.Account and b.Uid=c.Uid and c.Code = '1' and b.flag = 'A'
set Account = case when len(a.Account)<6
then a.ID+(REPLICATE('0', 6-len(a.Account))+a.Account))
else a.Account End
from a,b,c
where a.OldAccout = b.Account and b.Uid=c.Uid and c.Code = '1' and b.flag = 'A'
解决方案 »
- 求一条sql查询语句
- 求一更新数据触发器
- 这个更新怎么写?
- 在ms sql server 2000在企业管理器里怎样加唯一约束
- 一个简单的触发器怎么写?
- 那位高手帮忙解决几个问题?ALL、ANY、SOME关键字分别什么意思?如果在查询未尾加上COMPUTE子句给几个字段做个汇总,那么程序执行这条查询
- 如何将SQL2000的数据库文件降级到SQL7.0?使该数据库能在7.0上用?
- 幫忙,簡單的SQL文
- 求一条sql语句
- 小弟的一个表中的记录全是无序的,我要怎样在每次在sql server中打开这一个表的时候,看见的全是阿按id号排好序的记录?!
- 请问SQL2000 标准版是否支持双处理器或者更多!
- 求一SQL语句,大家帮帮我。
set Account = case when len(a.Account)<6
then a.ID+REPLICATE('0', 6-len(a.Account))+a.Account
else a.Account End
from a,b,c
where a.OldAccout = b.Account and b.Uid=c.Uid and c.Code = '1' and b.flag = 'A'
from A表 a join B表 b on a.Account=b.Account
join C表 c on b.Uid=c.Uid
where B.Flag = 'A'和C.Code='1'
from A表 a join B表 b on a.Account=b.Account
join C表 c on b.Uid=c.Uid
where B.Flag = 'A' and C.Code='1' and len(a.Account)<6
set b.Account = case when len(b.Account)<6
then cast(a.ID as char)+REPLICATE('0', 6-len(b.Account))+a.Account)
else cast(a.ID as char)+b.Account End
from a,b,c
where a.Account = b.Account and b.Uid=c.Uid and c.Code = '1' and b.flag = 'A'
set Account = a.ID+right('000000'+B.Account,6)
from
A , B , C
where
A.Account = B.Account
and
B.Uid = C.Uid
and
C.Code = '1'
and
B.flag = 'A'
and
len(B.Account) < 6
update b
set Account = case when len(a.Account)<6
then a.ID+REPLICATE('0', 6-len(a.Account))+a.Account
else a.Account End
from a,b,c
where a.OldAccout = b.Account and b.Uid=c.Uid and c.Code = '1' and b.flag = 'A'
这样加会不会有问题?
改成这个:update b
set b.Account = case when len(b.Account)<6
then cast(a.ID as char)+REPLICATE('0', 6-len(b.Account))+a.Account
else cast(a.ID as char)+b.Account End
from a,b,c
where a.Account = b.Account and b.Uid=c.Uid and c.Code = '1' and b.flag = 'A'
a.Id as char不能把int转成char,会报错.
declare @i int
set @i=1000000000
select @i
select cast(@i as char)这个转换没有问题
from A,B,C
where a.Account=b.Account and b.Uid=c.Uid --关联条件
and B.Flag = 'A' and C.Code='1' --更新条件
and len(b.Account)<6 --不足6位的才更新
from A,B,C
where a.Account=b.Account and b.Uid=c.Uid --关联条件
and B.Flag = 'A' and C.Code='1' --更新条件
and len(b.Account)<6 --不足6位的才更新
create table A(Account char(10),Id int)
insert A select '25001',11
union all select '002' ,12
union all select '3003' ,13create table B(Uid int,Account char(10),name varchar(10),Flag char(1))
insert B select 1,'25001','a','A'
union all select 2,'002' ,'b','A'
union all select 3,'3003' ,'c','B'create table C(Uid int,Code char(10))
insert C select 1,2
union all select 2,1
union all select 3,2
go--更新
update B set Account=rtrim(a.Id)+right('000000'+rtrim(b.Account),6)
from A,B,C
where a.Account=b.Account and b.Uid=c.Uid --关联条件
and B.Flag = 'A' and C.Code='1' --更新条件
and len(b.Account)<6 --不足6位的才更新--显示更新结果
select * from B
go--删除测试
drop table A,B,C/*--测试结果Uid Account name Flag
----------- ---------- ---------- ----
1 25001 a A
2 12000002 b A
3 3003 c B(所影响的行数为 3 行)
--*/
有没有更好的方法,没有的话再执行下面这条会不会有什么问题?
update B set Account=rtrim(a.Id) + rtrim(b.Account)
from A,B,C
where a.Account=b.Account and b.Uid=c.Uid --关联条件
and B.Flag = 'A' and C.Code='1' --更新条件
and len(b.Account)=6 --刚好6位的才更新
set b.Account = case when len(b.Account)<6
then cast(a.ID as varchar)+REPLICATE('0', 6-len(rtrim(b.Account)))+rtrim(a.Account)
else cast(a.ID as varchar)+rtrim(b.Account)
End
from a,b,c
where a.Account = b.Account and b.Uid=c.Uid and c.Code = '1' and b.flag = 'A'
--所以以为只处理不足6位的,如果是够6位的也要处理,则改成:
update B set Account=rtrim(a.Id)+right('000000'+rtrim(b.Account),6)
from A,B,C
where a.Account=b.Account and b.Uid=c.Uid --关联条件
and B.Flag = 'A' and C.Code='1' --更新条件
--and len(b.Account)<=6 --不足6位的才更新 --***不加这个条件就行了
from A,B,C
where a.Account=b.Account and b.Uid=c.Uid --关联条件
and B.Flag = 'A' and C.Code='1' --更新条件
from A,B,C
where a.Account=b.Account and b.Uid=c.Uid --关联条件
and B.Flag = 'A' and C.Code='1' --更新条件
and len(b.Account)<=6 --<=6位的才更新