update table variable

  • 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)

  • 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?

  • 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)

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

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

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply