TableVariable Update

Question

Post reply

TableVariable Update

ramana3327

SSCoach

Points: 19722
More actions
May 29, 2014 at 9:00 pm

#293835

Hi,
I am writing a query to update table variable. It is throwing me some error.
I have a table variable declared and inserted the data with three columns. I want to update col1 of that table variable when the second column of that table variable= one column from a physical table
update @MYtabvar set @Mytabvar.LatestDate=B.LatestDate from TableB B where @Mytabvar.id=B.ID
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Try this instead

update m

SET LatestDate=B.LatestDate

FROM TableB B

INNER JOIN @Mytabvar m

ON m.id=B.ID

This is completely untested not having any table definitions and so forth.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ramana3327 SSCoach Points: 19722 More actions · Answer 2

ramana3327

SSCoach

Points: 19722

May 30, 2014 at 12:36 am

#1717666

Thank you.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

You're welcome

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

Aside from having a working solution, do you understand why your query was wrong?

You cannot reference a table variable in the same way you do a table, you MUST use an alias as in Jason's code if you want to prefix a column.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

mister.magoo (5/30/2014)
Aside from having a working solution, do you understand why your query was wrong?
You cannot reference a table variable in the same way you do a table, you MUST use an alias as in Jason's code if you want to prefix a column.

Good point and thanks for following up on that.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ramana3327 SSCoach Points: 19722 More actions · Answer 6

Actually I think I am doing some thing wrong here.

Actually I declared variable as table and inserted some values into that table variables from select statement of output from two tables.

Then I write the update statement.

So it is failing. It is asking for the declare statement again.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

A table variable, like any other variable, is only in scope until the end of the batch. If you run the table variable declare, then as another batch run the update, the update will fail. Same as if you first ran a declare of any other variable, then ran later a query using that variable.

You can't run this:

DECLARE @i INT

then later highlight and run this and expect it to work

SET @i = 0

Table variables have the same scoping rules as any other variable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

And be aware that if you have batch separators (GO) the variables will go out of scope.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ramana3327 SSCoach Points: 19722 More actions · Answer 9

Yes. I am aware of Go statement but here I am not using any batch terminator.

Declare @Myvar Tab(Id int, Latesttime DateTime)

Insert into @Myvar (Id, LatestTime)

(select Id, Latesttime from TableA A join TableC C where A.Name=C.Name....)

Update @Myvar

SET V.LatestTIme=B.LatestDate

FROM TableB B

INNER JOIN @Myvar V

ON V.id=B.ID

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

are you running all of that in the same batch?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

ramana3327 SSCoach Points: 19722 More actions · Answer 11

Yes.

Sorry my bad some spelling mistake for declared table variable & Updating tablevariable.

Thanks everyone!