Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: Table Variable Problem

    Should this statement be possible using Table variables:


    UPDATE @tblISDData
    INNER JOIN @tblChemo ON @tblISDData.PATNO = @tblChemo.PATNO
    SET @tblISDData.CHEM = @tblChemo.CHEM,
    @tblISDData.CHEMDATE = @tblChemo.CHEMDATE

    If so, what am I doing wrong, Query Analyzer is saying "Incorrect syntax near the keyword 'INNER'."

    Cheers
    Last edited by mmcdonald; 01-23-06 at 12:15.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you know I do not often use table variables but it seems to me you are missing some fundamentals here like a from clause which should come after the set statements.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Thanks, I hear what you're saying but as I'm sure you are aware not all UPDATE statements require FROM - the fact I'm using table variables shouldn't affect this. I think it's something to do with the Inner Join - maybe table variables don't allow this - can anyone shed any light?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Oh sigh.... I will give you a fish. Try this..

    UPDATE @tblISDData SET @tblISDData.CHEM = @tblChemo.CHEM,
    @tblISDData.CHEMDATE = @tblChemo.CHEMDATE
    FROM @tblISDData
    INNER JOIN @tblChemo ON @tblISDData.PATNO = @tblChemo.PATNO

    I have never seen an inner join without a from. If this does not work I need to see more of the problem.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107
    Ahhh, Ok, thanks, but you do admit you can use an UPDATE statement without a FROM? Got my wires crossed there.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can omit the FROM clause only if there are no JOINs, and only one table is involved.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Talking

    Here's the answer, when using Table Variables joins only seem to work if you use table aliases:

    UPDATE I
    SET I.CHEM = C.CHEM,
    I.CHEMDATE = C.CHEMDATE
    FROM @tblISDData I
    INNER JOIN @tblChemo C ON I.PATNO = C.PATNO

    Thanks again for you input.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    really? I am going to have test that when I get a chance.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Wink

    Solution:

    UPDATE I
    SET I.CHEM = C.CHEM,
    I.CHEMDATE = C.CHEMDATE
    FROM @tblISDData I
    INNER JOIN @tblChemo C ON I.PATNO = C.PATNO

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi MM

    Hopefully the site will let me post this time...

    Your solution is the same as Thrasys but with aliases. Aliases are not actually required:

    Code:
    DECLARE @T1 TABLE (TheJoin1 SmallInt Identity(1, 1), TheValue1 Char(6))
    DECLARE @T2 TABLE (TheJoin2 SmallInt Identity(1, 1), TheValue2 Char(6)) 
    DECLARE @i AS TinyInt
     
    SELECT @i = 0
     
    WHILE @i <10 BEGIN
     
     INSERT INTO @T1 (TheValue1) VALUES ('Data' + CAST(@i AS VarChar(2)))
     INSERT INTO @T2 (TheValue2) VALUES (NULL)
     SELECT @i = @i +1
     
    END
     
    SELECT * FROM @T2
     
    UPDATE @T2
    SET TheValue2 = TheValue1
    FROM @T1 INNER JOIN @T2 ON 
     TheJoin1 = TheJoin2
     
    SELECT * FROM @T2
    Your original Syntax would work in Access for example (perhaps this is what you are thinking of) but not in SQL Server. You do need a FROM clause in SQL Server Update statements if you have a join.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - my first post on the SQL Server forum was along the lines of "why can't I update more than one table at a time with an update statement?" so the UPDATE syntax was about the first SQL-Server-Specific thing I learnt

    Quote Originally Posted by blindman
    You can omit the FROM clause only if there are no JOINs, and only one table is involved.
    Unless you use sub selects.

    Blimey - I am on fire today.
    Last edited by pootle flump; 01-24-06 at 07:59.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, table variables must be aliased in any but the simplest select statements. A minor inconvenience.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman
    Yes, table variables must be aliased in any but the simplest select statements. A minor inconvenience.
    Really? How come my installation merrily trotted through my code? I know it isn't exactly a paradigm setting query but it does include joins....

    In fact:
    Quote Originally Posted by BOL
    Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:
    INSERT INTO table_variable EXEC stored_procedure
    SELECT select_list INTO table_variable statements.
    These are the only limits BOL mentions (apart from scope etc).
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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