Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

TableVariable Update Expand / Collapse
Author
Message
Posted Thursday, May 29, 2014 9:00 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 553, Visits: 670
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
Post #1575933
Posted Thursday, May 29, 2014 10:22 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:29 PM
Points: 17,729, Visits: 15,594
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1575939
Posted Friday, May 30, 2014 12:36 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 553, Visits: 670
Thank you.
Post #1575952
Posted Friday, May 30, 2014 7:14 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:29 PM
Points: 17,729, Visits: 15,594
You're welcome



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1576059
Posted Friday, May 30, 2014 10:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:53 PM
Points: 1,780, Visits: 5,750
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1576192
    Posted Friday, May 30, 2014 10:30 AM


    SSCoach

    SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

    Group: General Forum Members
    Last Login: Today @ 7:29 PM
    Points: 17,729, Visits: 15,594
    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


    SQL RNNR

    Posting Performance Based Questions - Gail Shaw
    Posting Data Etiquette - Jeff Moden
    Hidden RBAR - Jeff Moden
    VLFs and the Tran Log - Kimberly Tripp
    Post #1576194
    Posted Friday, May 30, 2014 10:46 AM
    Mr or Mrs. 500

    Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

    Group: General Forum Members
    Last Login: Today @ 4:26 PM
    Points: 553, Visits: 670
    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.
    Post #1576200
    Posted Friday, May 30, 2014 11:14 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 1:14 PM
    Points: 39,977, Visits: 36,340
    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 2008, MVP
    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

    Post #1576213
    Posted Friday, May 30, 2014 11:19 AM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Today @ 6:55 PM
    Points: 3,636, Visits: 8,150
    And be aware that if you have batch separators (GO) the variables will go out of scope.


    Luis C.
    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?

    Forum Etiquette: How to post data/code on a forum to get the best help
    Post #1576216
    Posted Friday, May 30, 2014 11:48 AM
    Mr or Mrs. 500

    Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

    Group: General Forum Members
    Last Login: Today @ 4:26 PM
    Points: 553, Visits: 670
    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
    Post #1576219
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse