title: Update table variable
body: <p>I have a table variable <code>@searchResult</code>:</p>
  
  <pre><code>DECLARE @searchResult TABLE (
      [name_template] NVARCHAR(50),
      [record_id] INT,
      [record_name] NVARCHAR(50)
  );
  </code></pre>
  
  <p>And table <code>[records]</code>:</p>
  
  <pre><code>CREATE TABLE [records] (
      [record_id] INT IDENTITY(1, 1) PRIMARY KEY,
      [record_name] NVARCHAR(50)
  )
  </code></pre>
  
  <p><code>@searchResult</code> contains records with <code>[name_template]</code> filled only. I want to update it with latest <code>[record_id]</code> and <code>[record_name]</code> from <code>[records]</code> table that match <code>[name_template]</code>.  </p>
  
  <p>I've tried folowing SQL query with no success:</p>
  
  <pre><code>UPDATE @searchResult
  SET [record_id] = r.[record_id], [record_name] = r.[record_name]
  FROM (
      SELECT TOP 1
            r.[record_id]
          , r.[record_name]
      FROM [records] AS r
      WHERE r.[record_name] LIKE [name_template]
      ORDER BY r.[record_id] DESC
  ) AS r;
  </code></pre>
  
  <p>Error message: </p>
  
  <blockquote>
    <p>Invalid column name 'name_template'.</p>
  </blockquote>
  
  <p>What is correct syntax to update <code>@searchResult</code> with desired values?</p>