CODEWITHSUNDEEP
sql-server
Meysam Tolouee
Meysam Tolouee

Reputation: 579

How Use table variable in where condition of Update statement

Here is My Query:

Declare @ParentIDMap Table (AccTypeID BigInt, FullNo nvarchar(50), Branch BigInt, Code Varchar, ID BigInt)

Insert  @ParentIDMap (AccTypeID, Branch, FullNo, Code)
    Values  (1, 5, '01111', 2),
            -- Some other rows
            (8, 5, '04121', 2)

Update  @ParentIDMap
    Set ID =  Book.ID
    From    Accounting.ACNT.Book
    Where   Book.FullNo = @ParentIDMap.FullNo + @ParentIDMap.Code

The Error: Must declare the scalar variable '@ParentIDMap'

Upvotes: 2

Views: 1605

Answers (1)

StuartLC
StuartLC

Reputation: 107327

You'll need to reference @ParentIdMap in the FROM clause in order to be able to update (SqlServer differs from other RDBMS in this way). You can also switch to a join:

Update  p
    Set p.ID =  Book.ID
    From    Accounting.ACNT.Book 
      INNER JOIN @ParentIDMap p
      ON Book.FullNo = p.FullNo + p.Code;

SqlFiddle here

Upvotes: 2

Related Questions