谁能告诉我这段代码我语法怎么错了，该怎么改

Question

/*返回一个字符串中某一个字符第n次出现的位置的函数*/
create function f_firstposition(@Str varchar(8000),@Strsep varchar(10),@AppPos int)
returns int
begin
      declare @i int
      declare @ii int
      set @Str=rtrim(ltrim(@Str))
      set @i=1
          select @ii=charindex(@StrSep,@Str)
          if @i=@AppPos
             return @ii
          else
          while @AppPos>@i
          begin
                if charindex(@StrSep,right(@Str,len(@Str)-@ii))<>0
                select @ii=charindex(@StrSep,right(@Str,len(@Str)-@ii))+@ii
           else
                set @ii=0
                set @i=@i+1
          end
   return @ii
endupdate  会计科目表  as a 
set a.科目编码=b.sid
  (select b.sid
    case  when class=1 then substring(b.sid,1,3)
          when class=2 then substring(b.sid,1,3)+right(replicate('00',2)+ substring(b.sid,5,dbo.f_firstposition(b.sid,'.',2)-1-dbo.f_firstposition(b.sid,'.',1)),2) 
          when class=3 then substring(b.sid,1,3)+right(replicate('00',2)+ substring(b.sid,dbo.f_firstposition(b.sid,'.',1)+1,dbo.f_firstposition(b.sid,'.',2)-1-dbo.f_firstposition(b.sid,'.',1)),2)+right(replicate('00',2)+ 
--
substring(b.sid,dbo.f_firstposition(b.sid,'.',2)+1,dbo.f_firstposition(b.sid,'.',3)-1-dbo.f_firstposition(b.sid,'.',2)),2)
          when class=4 then substring(b.sid,1,3)+right(replicate('00',2)+ substring(b.sid,dbo.f_firstposition(b.sid,'.',1)+1,dbo.f_firstposition(b.sid,'.',2)-1-dbo.f_firstposition(b.sid,'.',1)),2)+right(replicate('00',2)+ 
--
substring(b.sid,dbo.f_firstposition(b.sid,'.',2)+1,dbo.f_firstposition(b.sid,'.',3)-1-dbo.f_firstposition(b.sid,'.',2)),2)+right(replicate('00',2)+ substring(b.sid,dbo.f_firstposition(b.sid,'.',3)
--
+1,dbo.f_firstposition(b.sid,'.',4)-1-dbo.f_firstposition(b.sid,'.',3)),2)FROM [ZJZX].[ACCOUNTSECTIONS]  as b
   )
where a.帐套=b.setid
      a.科目名称=b.ABBREVIATION
      a.科目全称=b.name
      a.科目级别=b.class
[/code]

Answer 1

就是我update的时候语法错误啊，我感觉对的么

Answer 2

update a
set ....
from 会计科目表 as a, (select .... from ) as b
where a.[] = b.[] and ...

Answer 3

 set a.科目编码=b.sid 后面加个了个（）这种写法有问题

Answer 4

不行额，就是提示update语法错误
是不是我写的自定义函数后用update就报错额？

Answer 5

when class=4 then substring(b.sid,1,3)+right(replicate('00',2)+ substring(b.sid,dbo.f_firstposition(b.sid,'.',1)+1,dbo.f_firstposition(b.sid,'.',2)-1-dbo.f_firstposition(b.sid,'.',1)),2)+right(replicate('00',2)+  
--
substring(b.sid,dbo.f_firstposition(b.sid,'.',2)+1,dbo.f_firstposition(b.sid,'.',3)-1-dbo.f_firstposition(b.sid,'.',2)),2)+right(replicate('00',2)+ substring(b.sid,dbo.f_firstposition(b.sid,'.',3)
--
+1,dbo.f_firstposition(b.sid,'.',4)-1-dbo.f_firstposition(b.sid,'.',3)),2)
楼主.大概看了一下.你检查一下你的"括号"是不是对应的!

Answer 6

/*返回一个字符串中某一个字符第n次出现的位置的函数*/
create function f_firstposition(@Str varchar(8000),@Strsep varchar(10),@AppPos int)
returns int
begin
      declare @i int
      declare @ii int
      set @Str=rtrim(ltrim(@Str))
      set @i=1
          select @ii=charindex(@StrSep,@Str)
          if @i=@AppPos
             return @ii
          else
          while @AppPos>@i
          begin
                if charindex(@StrSep,right(@Str,len(@Str)-@ii))<>0
                select @ii=charindex(@StrSep,right(@Str,len(@Str)-@ii))+@ii
           else
                set @ii=0
                set @i=@i+1
          end
   return @ii
endupdate [text].[ZJZX].[ACCOUNTSECTIONS]
set sid =case when class=1 then substring(sid,1,3)
     when class=2 then substring(sid,1,3)+right(replicate('00',2)+ substring(sid,5,dbo.f_firstposition(sid,'.',2)-1-dbo.f_firstposition(sid,'.',1)),2) 
     when class=3 then substring(sid,1,3)+right(replicate('00',2)+ substring(sid,dbo.f_firstposition(sid,'.',1)+1,dbo.f_firstposition(sid,'.',2)-1-dbo.f_firstposition(sid,'.',1)),2)+right(replicate('00',2)+
substring(sid,dbo.f_firstposition(sid,'.',2)+1,dbo.f_firstposition(sid,'.',3)-1-dbo.f_firstposition(sid,'.',2)),2)
     when class=4 then substring(sid,1,3)+right(replicate('00',2)+ substring(sid,dbo.f_firstposition(sid,'.',1)+1,dbo.f_firstposition(sid,'.',2)-1-dbo.f_firstposition(sid,'.',1)),2)+right(replicate('00',2)+ 
substring(sid,dbo.f_firstposition(sid,'.',2)+1,dbo.f_firstposition(sid,'.',3)-1-dbo.f_firstposition(sid,'.',2)),2)+right(replicate('00',2)+ substring(sid,dbo.f_firstposition(sid,'.',3)
+1,dbo.f_firstposition(sid,'.',4)-1-dbo.f_firstposition(sid,'.',3)),2)end这个问题解决了，但是运行的时候还是出现update语法错误，

Answer 7

我现在就是直接更新原始表的sid，因为可能多张表要用到，但是update就是语法错误是不是update书写哪里错误了哇？