"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.

Answer 1

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.

Answer 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

Answer 3

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)