Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    28

    Unanswered: Update statement get executed but table remains the same

    Hello Experts,

    Im trying to fill in one of my table with the information which Im pulling from two different places (sys.Object & Information.schema.columns). So far Ive fetch and stored all the data what I need. My first table structure is as follows
    Create Table #TempTotalTable(
    DBname varchar(100),
    TBId int,
    TBName varchar(500),
    CapturedDate datetime,
    TBCreationData datetime,
    TBLastModifiedDate datetime,
    NoOfCol int
    )

    And second table structure is like this.
    create table #ColumnPerTable(
    DBName varchar(100),
    TBName varchar(500),
    NoOfColumn int
    )

    Now keeping in mind at some point I wouldve to update NoOfCol in #TempTotalTable though #ColumnPerTable I kept two fields same on both tables TBName & NoOfColumn. Now my problem is when I execute the following update statement

    update #TempTotalTable
    set NoOfCol = NoOfCol
    from #ColumnPerTable
    where #TempTotalTable.TBName = #ColumnPerTable.TBName

    It get executed with the message 235 rows get effected. However when I go back and do select on #TempTotalTable still NoOfCol shows null as follows.

    DBName TBId TBName CapturedDate TBCreationDate TBLastMoifiedDate NoOfCol
    Master 326292222 Account 2009-11-14 08:54:43.217 2009-10-28 14:22:49.813 2009-10-28 14:22:49.817 NULL
    Master 662293419 Account 2009-11-14 08:54:43.217 2009-10-28 14:22:49.813 2009-10-28 14:22:49.817 NULL
    Master 710293590 Account 2009-11-14 08:54:43.217 2009-10-28 14:22:49.813 2009-10-28 14:22:49.817 NULL

    Im not able to understand why? Can anybody see whats the problem here?

    Thanks a lot in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    UPDATE #TempTotalTable
       SET NoOfCol = #ColumnPerTable.NoOfCol
       FROM #ColumnPerTable
       WHERE  #TempTotalTable.TBName = #ColumnPerTable.TBName
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    UPDATE #TempTotalTable
       SET NoOfCol = #ColumnPerTable.NoOfCol
      FROM #TempTotalTable t1
    INNER
      JOIN #ColumnPerTable t2
        ON t1.TBName = t2.TBName
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Your tables don't have any keys! That's your first problem. You are using Microsoft's version of the UPDATE statement with its FROM and JOIN clauses. That syntax suffers a pretty nasty design flaw that can cause unpredictable results when the joining criteria is not unique.

    If you are using SQL Server 2008 then I suggest you replace the UPDATE with MERGE, which is standard SQL, doesn't suffer the same problems as UPDATE and is actually more efficient in many cases. Unfortunately not enough people seem to know this and I still see the old UPDATE...JOIN syntax being used far too much.

    MERGE INTO #TempTotalTable
    USING #ColumnPerTable
    ON #TempTotalTable.TBName = #ColumnPerTable.TBName
    WHEN MATCHED THEN UPDATE SET NoOfCol = #ColumnPerTable.NoOfColumn;

    In earlier versions it is still possible to use the standard UPDATE syntax which avoids the problems with the Microsoft UPDATE but unfortunately is often much less efficient:

    UPDATE #TempTotalTable
    SET NoOfCol =
    (SELECT NoOfColumn
    FROM #ColumnPerTable
    WHERE #TempTotalTable.TBName = #ColumnPerTable.TBName);

    Note that this statement is not necessarily equivalent to your original because it will update all rows even if there is no matching row in #ColumnPerTable. Depending on your data and keys it may or may not be necessary to add a WHERE clause as well.

    Hope this helps.

  5. #5
    Join Date
    Oct 2009
    Posts
    28
    Thank you so much guy for your replies to solve my problem and I learned from my mistake.

    Many Thanks again for showing me correct way.

    YOU ALL ARE BEST.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    thank you

    remember

    we are equal

    some just more so
    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.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Dportas is right, MERGE would be preferable if it works in asyed01's environment. I was just lazy and provided the piece needed to get the posted statement to work instead of explaining why it was dangerous and better options for accomplishing the same purpose. In this case I should have done more than just get the code working.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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