代码之家  ›  专栏  ›  技术社区  ›  Scott

SQL Server 2005语法错误

  •  0
  • Scott  · 技术社区  · 14 年前

    以下是SQL:

    CREATE TABLE dbo.TmpFeesToRules1(Name varchar, LookupId int)
    
    INSERT INTO dbo.TmpFeesToRules1(Name, LookupId)
    SELECT DISTINCT Name, 0 FROM Lending.Fee
    UNION SELECT DISTINCT Name, 0 FROM Lending.ApplicationFee
    
    INSERT INTO dbo.tblLookup (LookupType, LookupCode, LookupDesc, EditFlag, DeletedFlag, DefaultFlag)
    SELECT 'FEE_CODE', Name,  Name, 0, 0, 0
    FROM dbo.TmpFeesToRules1
    
    UPDATE dbo.TmpFeesToRules1
    SET
        LookupId = L.LookupID
    FROM
        dbo.tblLookup L
    INNER JOIN dbo.TmpFeesToRules1 T ON T.Name = L.LookupDesc
    WHERE
        L.LookupType = 'FEE_CODE'
    
    CREATE TABLE dbo.TmpFeesToRules2(FeeId int, Name varchar)
    
    INSERT INTO dbo.TmpFeesToRules2(FeeId, Name)
    SELECT FeeId, Name FROM Lending.ApplicationFee
    
    GO
    
    UPDATE Lending.ApplicationFee 
    SET Lending.ApplicationFee.FeeTypeId = T.LookupId
    FROM dbo.TmpFeesToRules2 TT
    INNER JOIN dbo.TmpFeesToRules1 T ON T.Name = TT.Name
    WHERE Lending.ApplicationFee.FeeId = TT.FeeId
    
    DROP TABLE dbo.TmpFeesToRules1
    DROP TABLE dbo.TmpFeesToRules2
    

    错误如下:

    对象名“dbo.TmpFeesToRules2”无效。
    执行失败,返回代码:1

    有什么想法吗?

    2 回复  |  直到 13 年前
        1
  •  6
  •   Martin Smith    14 年前

    与此无关 Go

    您正在更新 Lending.ApplicationFee FROM

    UPDATE Lending.ApplicationFee 
    SET Lending.ApplicationFee.FeeTypeId = T.LookupId
    FROM dbo.TmpFeesToRules2 TT
    INNER JOIN dbo.TmpFeesToRules1 T ON T.Name = TT.Name
    WHERE Lending.ApplicationFee.FeeId = TT.FeeId
    

    我想应该是这样的

    UPDATE Lending.ApplicationFee 
    SET Lending.ApplicationFee.FeeTypeId = T.LookupId
    FROM Lending.ApplicationFee join dbo.TmpFeesToRules2 TT ON <Some Condition>
    INNER JOIN dbo.TmpFeesToRules1 T ON T.Name = TT.Name
    WHERE Lending.ApplicationFee.FeeId = TT.FeeId
    
        2
  •  2
  •   SWeko    14 年前

    CREATE TABLE dbo.TmpFeesToRules2(FeeId int, Name varchar)
    
    INSERT INTO dbo.TmpFeesToRules2(FeeId, Name)
    SELECT FeeId, Name FROM Lending.ApplicationFee
    

    你可以这样做:

    SELECT FeeId, Name 
    INTO dbo.TmpFeesToRules2 
    FROM Lending.ApplicationFee
    

    它将在一个步骤中创建和填充表