June 6, 2009 at 5:06 am
DECLARE @t table (offset int IDENTITY(1,1), foo nvarchar(50),number int)
INSERT INTO @t (foo) VALUES ('one')
INSERT INTO @t (foo) VALUES ('two')
INSERT INTO @t (foo) VALUES ('three')
INSERT INTO @t (foo) VALUES ('four')
I have another table :
create table foo(foo nvarchar(50),number int)
I want to update @t column number
and value from table foo (column column in both are foo)
June 6, 2009 at 5:14 am
You haven't provided any sample data for the second table, so it is not clear to me what you are asking.
Have you tried a solution? What did you come up with, and where did you get stuck?
June 6, 2009 at 5:22 am
DECLARE @t table (offset int IDENTITY(1,1), foo nvarchar(50),number int)
INSERT INTO @t (foo) VALUES ('one')
INSERT INTO @t (foo) VALUES ('two')
INSERT INTO @t (foo) VALUES ('three')
INSERT INTO @t (foo) VALUES ('four')
I have another table :
create table test(foo nvarchar(50),number int)
insert into foo(one,1)
insert into foo(one1,2)
insert into foo(one2,3)
insert into foo(one3,4)
I want to
update @t
set number = test.number
where @t.foo=test.foo
comon column in both are foo)
June 6, 2009 at 5:28 am
Does this work?
update t
set number = test.number
FROM @t AS t INNER JOIN test on t.foo=test.foo
p.s. please in future test the sample code that you give us. There were several syntax errors in the code you cave to create and populate test.
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
June 6, 2009 at 5:46 am
yes but you have created table t not @t....
I have problem with table variable @t.......
table variable can't be updated as shown by me or u.
June 6, 2009 at 6:09 am
mjarsaniya (6/6/2009)
yes but you have created table t not @t....I have problem with table variable @t.......
table variable can't be updated as shown by me or u.
Hi,
t is an alias for @t. It's the same table. It is true you can't update a table variable directly using its variable name - you have to use an alias.
DECLARE @t TABLE (a INT NOT NULL)
INSERT @t (a) VALUES (1)
UPDATE t SET a = a + 1 FROM @t AS t -- note the alias
SELECT * FROM @t
June 6, 2009 at 6:53 am
mjarsaniya (6/6/2009)
yes but you have created table t not @t....
I assume you didn't try my code.... If you had and had queried the table variable, you'd have seen it did work. I did test it.
FROM @t t - From the table names @t which, for the rest of this query will be known by the alias name t.
table variable can't be updated as shown by me or u.
Odd, I've just shown they can
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
June 6, 2009 at 7:40 am
Thanks its working.........
and you are right I have not tried your code.
also u r right I have not give sample for my query in good manner.....
Next time i will take care for same
Thanks
June 6, 2009 at 8:20 am
mjarsaniya (6/6/2009)
Next time i will take care for same
Much appreciated.
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
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy