Updating Table Variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Updating Table Variables

Author

Topic

Sogartar
Starting Member

1 Post

Posted - 2006-10-05 : 23:53:24

I am having a difficulty updating a table variable.
Lets asume we are having the following tables:

Permanent tables:

table1(Column1 INT)
table2(Column1 INT)

Table variable:

@tableVar(Column1 INT)

Here is the code that causes the problem:

UPDATE @tableVar
SET Column1 = 1 --It really does not matter what is here
FROM table1
INNER JOIN table2
ON table1.Column1 = table1.Column1 --It really does not matter what is here
WHERE Column1 = table1.Column1

In bold is the line of the error, which is:
Ambiguous column name 'Column1'

If you remove the lines with the INNER JOIN, everything is fine:

UPDATE @tableVar
SET Column1 = 1 --It really does not matter what is here
FROM table1
WHERE Column1 = table1.Column1

In this case the SQL Server 2005 understands that when talking just about Column1, it should be refered to the table variable we are updating.
If you try having the table variable name as a prefix like that:

UPDATE @tableVar
SET Column1 = 1 --It really does not matter what is here
FROM table1
INNER JOIN table2
ON table1.Column1 = table1.Column1 --It really does not matter what is here
WHERE @tableVar.Column1 = table1.Column1

You will get the error:
Must declare the scalar variable "@tableVar"

I was than thinking to asign an alias to the name of the table variable that should be updated like this:

UPDATE @tableVar AS tabelVar
SET Column1 = 1 --It really does not matter what is here
FROM table1
INNER JOIN table2
ON table1.Column1 = table1.Column1 --It really does not matter what is here
WHERE tableVar.Column1 = table1.Column1

But this the server does not accept as valid code. If you refere to the T-SQL reference of SQL Server 2005, you will see that you can't alias the name of the table you are updating. I was thinking quite a bit on that and it will be nice if you can do that. Also I couldn't think of any conflict that it can have with the rest of the T-SQL syntax.
Anyway, if you know any solution to do update like this in one query, I will be thankful if you shear it.
I andvance I will ask you, not to post replays like, "Use temporary table."

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-06 : 00:07:20

[code]
UPDATE a
SET
Column1 = 1
FROM
@tableVar a
join
table1 b
on a.Column1 = b.Column1
join
table2 c
ON b.Column1 = c.Column1

[/code]

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-06 : 10:37:44

You cant prefix table variable name to refer column. You need to use alias name and refer it as MVJ's query

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources