Update table variable
<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>