Update statement works with temp table but not with table valued variables

Question

I defined this table:

CREATE TABLE #stagingtable
(
    id int identity(1,1),
    typeflag int default 0,
    resourcetype varchar(25),
    resource varchar(40),
    est int,
    planned int,
    actual int
)

And then I am looking for places where the resourcetype is not the same as the resourcetype in the previous row, so I wrote the following UPDATE:

UPDATE #stagingtable
SET typeflag = 1 
WHERE id = (
    SELECT min(id)
    FROM #stagingtable
)
OR resourcetype <> (
    SELECT resourcetype
    FROM #stagingtable rt2
    WHERE rt2.id = (
        SELECT MAX(id) 
        FROM #stagingtable rt3
        WHERE rt3.id < #stagingtable.id
    )
)

This worked perfectly. However, the environment I am in won't let me use temp tables (RDL!). So I changed my table to a table-valued variable:

DECLARE @stagingtable TABLE
(
    id int identity(1,1),
    typeflag int default 0,
    resourcetype varchar(25),
    resource varchar(40),
    est int,
    planned int,
    actual int
)

But the following code doesn't work. 

UPDATE @stagingtable
SET typeflag = 1 
WHERE id = (
    SELECT min(id)
    FROM @stagingtable
)
OR resourcetype <> (
    SELECT resourcetype
    FROM @stagingtable rt2
    WHERE rt2.id = (
        SELECT MAX(id) 
        FROM @stagingtable rt3
        WHERE rt3.id < @stagingtable.id
    )
)

I get the message:

Msg 137, Level 16, State 1, Line 431 Must declare the scalar variable "@stagingtable".

Is there a way to change the update statement so it works?

@variables are local to the batch. i assume you have a GO command somewhere or are running this elsewhere that is accessible. You can try @@variable but if you are trying to keep this variable around for any time it isn't the way to go. why not just use a #temp? — S3S, May 22 '17 at 19:02

score 5 · Answer 1 · answered May 22 '17 at 19:03

5

Your query is almost fine. You only need to reference the table variable by the alias you gave it, on this last row:

WHERE rt3.id < @stagingtable.id

DECLARE @stagingtable TABLE
(
    id int identity(1,1),
    typeflag int default 0,
    resourcetype varchar(25),
    resource varchar(40),
    est int,
    planned int,
    actual int
)


UPDATE @stagingtable
SET typeflag = 1 
WHERE id = (
    SELECT min(id)
    FROM @stagingtable
)
OR resourcetype <> (
    SELECT resourcetype
    FROM @stagingtable rt2
    WHERE rt2.id = (
        SELECT MAX(id) 
        FROM @stagingtable rt3
        WHERE rt3.id < rt2.id
    )
)

answered May 22 '17 at 19:03

Rigerta

3,959
15
26

1

nice catch on the alias – S3S May 22 '17 at 19:08
I don't think this works because rt2.id is not the same as stagingtable.id – Geoff Snowman May 22 '17 at 19:09
@GeoffSnowman what do you need to do there? the main table you cannot access like that as a simple variable, it is still a table, you will need a join to get the rows from it... – Rigerta May 22 '17 at 19:11
See my answer below. – Geoff Snowman May 22 '17 at 19:12
I just checked this by running the query and the rt3.id – Geoff Snowman May 22 '17 at 19:16
Thanks for the suggestion, anyway. :) – Geoff Snowman May 22 '17 at 19:17
You should mark your answer as the right answer if anyone ever has the same issue. – Rigerta May 22 '17 at 19:18
You can't accept your own answer until the question has been up for a while. Hour or two, I think. – Donnie May 22 '17 at 19:49

score 1 · Answer 2 · edited May 22 '17 at 19:41

1

I managed to come up with the correct syntax, which is to add square brackets around the variable name:

UPDATE @stagingtable
SET typeflag = 1 
WHERE id = (
    SELECT min(id)
    FROM @stagingtable
)
OR resourcetype <> (
    SELECT resourcetype
    FROM @stagingtable rt2
    WHERE rt2.id = (
        SELECT MAX(id) 
        FROM @stagingtable rt3
        WHERE rt3.id < [@stagingtable].id
    )
)

edited May 22 '17 at 19:41

Rigerta

3,959
15
26

answered May 22 '17 at 19:11

Geoff Snowman

314
2
10

1

Use `update ... from` so you can alias the outermost table. As now, it's hard to read this and tell what's going on, and you're going to confuse someone later. (https://stackoverflow.com/questions/4981481/how-to-write-update-sql-with-table-alias-in-sql-server-2008) – Donnie May 22 '17 at 19:32

Update statement works with temp table but not with table valued variables

2 Answers2