Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: update a table varialbe

    Hello.

    In a SP, I have declared several table variables like this:

    DECLARE @outcome table (column1, column2, column3, column4.....)
    DECLARE @tmp5 table (column1, column2, column3, column4.....)
    DECALRE @tmp3 table ((column1, column2, column3, column4.....)

    and are trying to do the following update:

    update @tmp3
    set column1=(
    select count(o.column1)
    from @outcome o, @tmp5 t5
    where o.column2 = @tmp3.column2
    and t5.column3= o.column3
    and o.column4is null
    )

    But, I can't get this procedure to compile, since the compiler gives me the following message: 'Must declare the variable @tmp3', referencing the line in my where-clause of my subquery.

    I asume that I have reached a limitation of table variables, and I could really need a few thoughts regarding any workaround of this. Is it possible to rewrite my updatestatement in any way that make this update run?

    regards
    Tore Tjotta

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: update a table varialbe

    It's a problem with your update statement..

    Code:
    DECLARE @outcome table (column1 int, column2 int, column3 int, column4 int)
    DECLARE @tmp5 table (column1 int, column2 int, column3 int, column4 int)
    DECLARE @tmp3 table (column1 int, column2 int, column3 int, column4 int)
    
    UPDATE @tmp3
       SET column1=(SELECT count(o.column1)
    		  FROM @outcome o, @tmp5 t5, @tmp3 a
    		 WHERE o.column2 = a.column2
    		   AND t5.column3= o.column3
    		   AND o.column4 is null
    )

    But I'm not sure what you're really trying to do...

    SELECT COUNT(whatever) is going to always give you 1 result set...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Posts
    4

    Re: update a table varialbe

    I'm trying to update my @tmp3.column1 with a number (as a result from select count(....)).

    This update works just fine if I use normal temp-tables like #tmp3.

    Tore

    Originally posted by Brett Kaiser
    It's a problem with your update statement..

    Code:
    DECLARE @outcome table (column1 int, column2 int, column3 int, column4 int)
    DECLARE @tmp5 table (column1 int, column2 int, column3 int, column4 int)
    DECLARE @tmp3 table (column1 int, column2 int, column3 int, column4 int)
    
    UPDATE @tmp3
       SET column1=(SELECT count(o.column1)
    		  FROM @outcome o, @tmp5 t5, @tmp3 a
    		 WHERE o.column2 = a.column2
    		   AND t5.column3= o.column3
    		   AND o.column4 is null
    )

    But I'm not sure what you're really trying to do...

    SELECT COUNT(whatever) is going to always give you 1 result set...

  4. #4
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    is your select query working fine if run seperately?
    add table 3 in the "from" tables list.

  5. #5
    Join Date
    Oct 2003
    Posts
    4
    The select works just fine. I can't add table 3 to the from list, since the result of the update will be wrong. @tmp3 in the where-clause is used as a reference to the outer update. Without this reference, the update will set the same value in column1 for all rows. And that is not as it is suppose to be.

    Tore


    Originally posted by rohitkumar
    is your select query working fine if run seperately?
    add table 3 in the "from" tables list.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    When you reference a table variable in a join or a where clause you have to give it an alias.

    try this:

    update tmp3
    set column1=(
    select count(o.column1)
    from @outcome o,
    @tmp5 t5
    where o.column2 = tmp3.column2
    and t5.column3= o.column3
    and o.column4is null
    )
    from @tmp3 tmp3

    ...and you really should connect your tables using joins rather than in the WHERE clause.

    blindman

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This, for example, is better syntax using subquery:

    update tmp3
    set column1 = SubTotals.Total
    from @tmp3 tmp3
    inner join
    (select o.column2, count(o.column1) Total
    from @outcome o
    inner join @tmp5 t5 on o.column3 = t5.column3
    where o.column4is null
    group by o.column2) SubTotals
    on tmp3.column2 = SubTotals.column2

    blindman

  8. #8
    Join Date
    Oct 2003
    Posts
    4
    Thanks, that's exactly what I needed. And yes, I really should use joins, and some day I will start doing so.

    Tore

    Originally posted by blindman
    When you reference a table variable in a join or a where clause you have to give it an alias.

    try this:

    update tmp3
    set column1=(
    select count(o.column1)
    from @outcome o,
    @tmp5 t5
    where o.column2 = tmp3.column2
    and t5.column3= o.column3
    and o.column4is null
    )
    from @tmp3 tmp3

    ...and you really should connect your tables using joins rather than in the WHERE clause.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •