ALTER PROCEDURE [dbo].[MusicClub_Interface_ChooseLuckUserForDelayActivity] 
@PrizePolyNo int
AS
UPDATE dbo.PrizeUserInfo
SET dbo.PrizeUserInfo.PrizeLevel= (
SELECT PrizeLevel FROM PrizeLevelAndSeqNumSuffix 
WHERE 
dbo.PrizeLevelAndSeqNumSuffix.IsSkipThisSuffix='0' AND 
dbo.PrizeLevelAndSeqNumSuffix.PrizePolyNo=3 

)
WHERE
RIGHT(dbo.PrizeUserInfo.SeqNumber,LEN(dbo.PrizeLevelAndSeqNumSuffix.PrizeSeqNumSuffix)) = dbo.PrizeLevelAndSeqNumSuffix.PrizeSeqNumSuffix AND 
dbo.PrizeUserInfo.PrizePolyNo = @PrizePolyNo报错:列前缀 'dbo.PrizeLevelAndSeqNumSuffix' 与查询中所用的表名或别名不匹配。
 检查了半天,没找到原因,哪位大神能帮忙看一下啊,先谢谢了

解决方案 »

  1.   


    ALTER PROCEDURE [dbo].[MusicClub_Interface_ChooseLuckUserForDelayActivity]  
    @PrizePolyNo int
    AS
    UPDATE dbo.PrizeUserInfo
    SET dbo.PrizeUserInfo.PrizeLevel= (
    SELECT PrizeLevel FROM dbo.PrizeLevelAndSeqNumSuffix  
    WHERE  
    dbo.PrizeLevelAndSeqNumSuffix.IsSkipThisSuffix='0' AND  
    dbo.PrizeLevelAndSeqNumSuffix.PrizePolyNo=3  )
    WHERE
    RIGHT(dbo.PrizeUserInfo.SeqNumber,LEN(dbo.PrizeLevelAndSeqNumSuffix.PrizeSeqNumSuffix)) = dbo.PrizeLevelAndSeqNumSuffix.PrizeSeqNumSuffix AND  
    dbo.PrizeUserInfo.PrizePolyNo = @PrizePolyNo
      

  2.   

    有个地方少了dbo.     
    上方我标志的地方没有显示出颜色
      

  3.   

    我将dbo 都加上了 ,还是那个错误 ...
      

  4.   

    --TRYALTER PROCEDURE [dbo].[MusicClub_Interface_ChooseLuckUserForDelayActivity]  
    @PrizePolyNo int
    AS
    DECLARE @PrizePolyNo intUPDATE dbo.PrizeUserInfo
    SET dbo.PrizeUserInfo.PrizeLevel= PrizeLevelAndSeqNumSuffix.PrizeLevel
    FROM dbo.PrizeUserInfo,PrizeLevelAndSeqNumSuffix  
    WHERE  
    dbo.PrizeLevelAndSeqNumSuffix.IsSkipThisSuffix='0' 
    AND  dbo.PrizeLevelAndSeqNumSuffix.PrizePolyNo=3  
    AND RIGHT(dbo.PrizeUserInfo.SeqNumber,LEN(dbo.PrizeLevelAndSeqNumSuffix.PrizeSeqNumSuffix)) 
    = dbo.PrizeLevelAndSeqNumSuffix.PrizeSeqNumSuffix 
    AND  dbo.PrizeUserInfo.PrizePolyNo = @PrizePolyNo
      

  5.   

    我算是看出来哪里有问题了,你后面的where语句里根本就不能那样直接用dbo.PrizeLevelAndSeqNumSuffix.PrizeSeqNumSuffix,我帮你改改
      

  6.   


    -->try
    ALTER PROCEDURE [dbo].[MusicClub_Interface_ChooseLuckUserForDelayActivity]  
    @PrizePolyNo int
    AS
    UPDATE dbo.PrizeUserInfo SET PrizeLevel=b.PrizeLevel
    FROM dbo.PrizeUserInfo a
    INNER JOIN
    (
    SELECT PrizeLevel,PrizeSeqNumSuffix FROM PrizeLevelAndSeqNumSuffix 
    WHERE  IsSkipThisSuffix='0' AND  PrizePolyNo=3  
    )b
    on a.PrizePolyNo=b.PrizePolyNo
    WHERE RIGHT(a.SeqNumber,LEN(b.PrizeSeqNumSuffix)) = b.PrizeSeqNumSuffix AND a.PrizePolyNo = @PrizePolyNo
      

  7.   


    原来是少了from  ,谢谢这位 姐了  !!!