Updating a table from a table variable

Question

I have a SQL query that declares a table within it.

Declare @t table(tagname nvarchar(50), Value float, timestamp datetime)

I then insert some date into this table. Once this is done I want to update another table (already created) from @t.

Something along the lines of:

UPDATE Optimiser_tagData
SET Optimiser_tagData.value = @t.value
where Optimiser_tagData.tagName = @t.tagName

This obviously doesn't work and I get this error:

Must declare the scalar variable "@t"

I am sure I am missing something very easy but I can't quite figure it out.

Answer 1

Your update statement should be like below. You have to apply Join between Table variable and Optimiser_tagData. And you should run update statement as a whole(Table variable declaration etc)

UPDATE Optimiser
    SET Optimiser.value = t.value
    from Optimiser_tagData Optimiser
    join @t t
    on Optimiser.tagName = t.tagName