"Must declare the scalar variable" when trying to run UPDATE statement in SQL

Question

Trying to update two tables (City and Location) using two table variables (@NameZip, @NameZip2). The city names have ZipCodes and ZipCodes have Names instead of vice versa. Updates are changing City names and ZipCodes where they were wrongly entered. But getting the error:

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

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

The query I wrote:

--first table variable
DECLARE @NameZip TABLE 
                 (
                     Zip_Code NVARCHAR(100),
                     Name NVARCHAR(100),
                     id_city INT
                 )

--second table variable
DECLARE @NameZip2 TABLE
                  (
                      Zip_Code nvarchar(100),
                      Name NVARCHAR(100),
                      id_city INT
                  )

--inserting into first table variable from City and Location table
INSERT INTO @NameZip (Zip_code, Name, id_city)
    SELECT B.Zip_Code, A.Name, A.id_city
    FROM City A 
    INNER JOIN Location B ON A.id_city = B.id_city 
                          AND Name NOT LIKE '%[^0-9]%'

--inserting into second table variable from first table variable
INSERT INTO @NameZip2(Zip_code, Name, id_city)
    SELECT Name, Zip_Code, id_city
    FROM @NameZip

UPDATE City 
SET Name = (SELECT Name FROM @NameZip2)
WHERE City.id_city = @NameZip2.id_city -- I get error on this line

UPDATE Location
SET Zip_Code = (SELECT Zip_Code FROM @NameZip2)
WHERE Zip_Code = @NameZip.Zip_Code -- I get error on this line

Any inputs regarding this would be appreciated.

score 3 · Answer 1 · answered Apr 06 '18 at 16:30

3

use update from join syntax

update c
set Name = z.Name 
from City C
inner join @NameZip2 z on c.id_city =z.id_city

do the same for location table update as well.

answered Apr 06 '18 at 16:30

Pரதீப்

91,748
19
131
172

score 2 · Accepted Answer · answered Apr 06 '18 at 16:30

2

You want to do this with a join... but your table variables aren't really needed.

update c
set c.Name = n.Name
from City c
inner join @NameZip2 n on n.id_city = c.id_city

update L
set L.Zip_Code = n.Zip_Code
from Location L
inner join
@NameZip2 n on n.Zip_Code = L.Zip_Code

Can be written as...

update c
set c.Name = n.Name, c.Zip_Code = n.Zip_Code
from City c
inner join    
    (SELECT B.Zip_Code, A.Name, A.id_city
    FROM City A 
    INNER JOIN Location B ON A.id_city = B.id_city 
                          AND Name NOT LIKE '%[^0-9]%') n

answered Apr 06 '18 at 16:30

S3S

24,809
5
26
45

I get your point sir. Sorry for marking my answer as the right one. I'm accepting this answer because, it shows the other efficient way of reaching at the solution. Thanks for the idea! – schikkamksu Apr 23 '18 at 20:01
1

No need to cal me sir or apologize good mate. I was just confused @schikkamksu – S3S Apr 23 '18 at 20:12

score 1 · Answer 3 · answered Apr 06 '18 at 16:39

While the other answers can help you change your code, I think it's interesting to explain what's wrong with the code:

SET Name = (SELECT Name FROM @NameZip2)

This line can give you an error. If you're using = operator you must ensure the expression will return only one value. Even you being right that @NameZip2 has only one record, this is not a good approach, you could do this:

SET Name = (SELECT Top 1 Name FROM @NameZip2)

And this line:

WHERE Zip_Code = @NameZip.Zip_Code

Will not work because @NameZip is a table, and you should use a SELECT command instead of a =, this way:

WHERE Zip_Code = (SELECT TOP 1 Zip_Code FROM @NameZip)

Just to continue with Ricardo's thought, the dot notation in your original code makes me think you're trying to treat the table variable as though it were an array. That's not irrational, but SQL Server doesn't let you do that. :) — Eric Brandt, Apr 06 '18 at 16:42

"Must declare the scalar variable" when trying to run UPDATE statement in SQL

3 Answers3