Post reply

Update @table with join

  • December 7, 2011 at 8:16 am

    #264416

    Hi, I have two table variables with two fields each. The definitions are as below:-

    declare @DocData table (fileId uniqueidentifier, PDFExists bit)

    declare @PDFData table (fileId uniqueidentifier, OriginalFileId uniqueidentifier)

    Now, I want to update the column PDFExists =1 where @DocData.fileId=@PDFData.OriginalFileId. I tried

    update @DocData set PDFExists=1 where @DocData.fileId=@PDFData.OriginalFileId

    But I get error saying @DocData is not declared.

    What is wrong in the update query?

  • December 7, 2011 at 8:26 am

    #1418144

    update dd set PDFExists=1

    FROM @DocData dd INNER JOIN @PDFData pdf ON dd.fileId=pdf.OriginalFileId

    Other than the missing join, the table variables either need to be aliased or enclosed in []

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 9, 2011 at 8:32 am

    #1419426

    Thanks for the quick reply..... It worked with that

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply