Update @table with join

• geo123abram

  SSChasing Mays

  Points: 656

  More actions

  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?

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• geo123abram

  SSChasing Mays

  Points: 656

  More actions

  December 9, 2011 at 8:32 am

  #1419426

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