Update table variable

Question

I have a table variable @searchResult:

DECLARE @searchResult TABLE (
    [name_template] NVARCHAR(50),
    [record_id] INT,
    [record_name] NVARCHAR(50)
);

And table [records]:

CREATE TABLE [records] (
    [record_id] INT IDENTITY(1, 1) PRIMARY KEY,
    [record_name] NVARCHAR(50)
)

@searchResult contains records with [name_template] filled only. I want to update it with latest [record_id] and [record_name] from [records] table that match [name_template].

I've tried folowing SQL query with no success:

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;

Error message:

Invalid column name 'name_template'.

What is correct syntax to update @searchResult with desired values?

Look at your query being used for an update. I assume the table records does not have a column named name_template? It is is your table variable. Remember your query has to execute on its own. That means in this case you have to join to your table variable in the query. — Sean Lange
– Sean Lange, Commented Mar 28, 2016 at 13:30
quotes missing ..like 'name_template' or like '%name_template%' — TheGameiswar
– TheGameiswar, Commented Mar 28, 2016 at 13:32
Possible duplicate of update one table with data from another — Tab Alleman
– Tab Alleman, Commented Mar 28, 2016 at 13:32

Pradeep Kumar · Accepted Answer · 2016-03-28 14:20:40Z

8

You need to do a CROSS APPLY on the tables.

UPDATE @searchResult
SET [record_id] = r.[record_id], 
    [record_name] = r.[record_name]
FROM @searchResult SR
CROSS APPLY (
    SELECT TOP 1 *
    FROM [records]
    WHERE [record_name] LIKE [name_template]   -- Your wish, but do you really need LIKE matching??
    ORDER BY [record_id] DESC
) AS r;

answered Mar 28, 2016 at 14:20

Pradeep Kumar

7,0294 gold badges24 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zohar Peled · Accepted Answer · 2016-03-28 14:02:20Z

4

Try this:

UPDATE t
SET [record_id] = r.[record_id], 
    [record_name] = r.[record_name]
FROM @searchResult t
INNER JOIN 
(
    SELECT MAX([record_id]) As [record_id]
            ,[record_name]
    FROM [records]
    GROUP BY [record_name] 
) r
ON r.[record_name] LIKE t.[name_template];

Update:
Seems to be working fine from what I've tested:

Create table and table variable:

CREATE TABLE [records] (
    [record_id] INT IDENTITY(1, 1) PRIMARY KEY,
    [record_name] NVARCHAR(50)
)

DECLARE @searchResult TABLE (
    [name_template] NVARCHAR(50),
    [record_id] INT,
    [record_name] NVARCHAR(50)
);

Populate with sample data:

INSERT INTO [records] ([record_name]) VALUES('a'), ('a'), ('a'), ('b'), ('b')

INSERT INTO @searchResult ([name_template]) VALUES ('a'), ('b')

Update table variable:

UPDATE t
SET [record_id] = r.[record_id], 
    [record_name] = r.[record_name]
FROM @searchResult t
INNER JOIN 
(
    SELECT MAX([record_id]) As [record_id]
            ,[record_name]
    FROM [records]
    GROUP BY [record_name] 
) r
ON r.[record_name] LIKE t.[name_template];

Check results:

SELECT *
FROM records

SELECT *
FROM @searchResult

DROP TABLE records

Results:

records
record_id   record_name
----------- -----------
1           a
2           a
3           a
4           b
5           b

@searchResult
name_template   record_id   record_name
-------------   ---------   -----------
a               3           a
b               5           b

edited Mar 28, 2016 at 14:02

answered Mar 28, 2016 at 13:35

Zohar Peled

82.9k11 gold badges79 silver badges134 bronze badges

5 Comments

Egor Over a year ago

This query works, but it updates [record_id] and [record_name] not with latest data.

Egor Over a year ago

Thanks for your efforts. Here is sample when it failes to updated with latest data (keep your test data and add this one): INSERT INTO [records] ([record_name]) VALUES('ab'); INSERT INTO @searchResult ([name_template]) VALUES ('a%') I expect @searchResult for [name_template] 'a%' after update statement to contain [record_id] of 'ab'

Zohar Peled Over a year ago

For this you will need a dynamic sql solution. Sql server will not treat the % inside the table variable as a wildcard, it will treat it as a part of the string. However, there are ways around that, if you can change the table variable.

Egor Over a year ago

I'm not sure whether there is special setting responsible for that, but in my case % in table variable is treated as I expect it to be (match any amount of characters)

Zohar Peled Over a year ago

well, seems like it's my mistake. At least you got an answer to your question., and a lesson of the importance of sample data...

K Scandrett · Accepted Answer · 2024-11-28 15:55:53Z

0

The most straightforward approach is to rename the column in the table variable to something unique, then the ambiguity disappears:

DECLARE @searchResult TABLE (
    [name_template] NVARCHAR(50),
    [record_id_1] INT,
    [record_name_1] NVARCHAR(50)
);

answered Nov 28, 2024 at 15:55

K Scandrett

16.6k4 gold badges43 silver badges69 bronze badges

Collectives™ on Stack Overflow

Update table variable

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related