Need help - 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

General SQL Server Forums

New to SQL Server Programming

Need help

Author

Topic

Leo_Don
Starting Member

42 Posts

Posted - 2008-11-03 : 09:39:05

Hi friends

I cant execte the following Qurery.. can yo let me know what the error i am making

update
dbo.@TESTTABLE

set code = dbo.table1.CODE

Thanx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 09:42:58

[code]update t1
set t1.code = t2.CODE
FROM @TESTTABLE t1
INNER JOIN dbo.table1 t2
ON t1.linkingfield=t2.linkingfield[/code]

replace linking field with actual fieldnames

EDIT: removed owner name from table variable as per Madhi's comments

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-03 : 09:43:35

update t1
set code = t2.CODE
from @TESTTABLE as t1 inner join dbo.table1 as t2
on t1.key_column=t2.key_column

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-03 : 09:44:04

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-03 : 09:45:25

quote:
Originally posted by visakh16

update t1
set t1.code = t2.CODE
FROM dbo.@TESTTABLE t1
INNER JOIN dbo.table1 t2
ON t1.linkingfield=t2.linkingfield

replace linking field with actual fieldnames

Not possible to qualify object owner for table variable

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 09:49:05

ah..good catch...didnt notice its a table variable

post edited.

Leo_Don
Starting Member

42 Posts

Posted - 2008-11-03 : 09:52:13

i am getting this error

The "@TESTTABLE" table variable must be declared

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 09:53:57

quote:
Originally posted by Leo_Don

i am getting this error

The "@TESTTABLE" table variable must be declared

where you've declared the table variable. you need to make sure you run declaration along with above query in same batch as table variable will be out of scope only you ends a batch. show your full query batch if you need more help.

Leo_Don
Starting Member

42 Posts

Posted - 2008-11-03 : 10:04:08

quote:
Originally posted by visakh16

quote:
Originally posted by Leo_Don

i am getting this error

The "@TESTTABLE" table variable must be declared

where you've declared the table variable. you need to make sure you run declaration along with above query in same batch as table variable will be out of scope only you ends a batch. show your full query batch if you need more help.

It Say O Colums Effected

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 10:06:11

do you have enough data in both the tables which are related on the specified columns?

Leo_Don
Starting Member

42 Posts

Posted - 2008-11-03 : 10:11:45

I have data in table1

below is the sample

Table1

Code
-------
1
2
3
4

testtable

code
------

in the test table i dont have any data...

all the colums are null

I want to copy the code from table1 to testtable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-03 : 10:20:35

then it should be insert not update

insert into testtable (code)
select code from Table1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources