Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Unanswered: update with join: does not work sometimes

    I have an sql query like this in my stored procedure

    update #temp_A
    set A.col_2 = B.col_2
    from #temp_A A,
    #temp_B B
    where A.col_1 = B.col_1

    It mostly works; except sometimes it does not update col_2, i.e. though there are matching rows in #temp_B, data from temp_B (col_2) is not copied into #temp_A (col_2).

    Has anyone come across anything similar before? I was suspecting that the problem might be due to the qualifier "A" in the line "set". But I want to be sure before I change it.


    Additional information:
    1.ASE 11.9.2.6
    2.Clustered index on #temp_A (col_1)
    3.Typical batch size of 100 rows in each table

  2. #2
    Join Date
    Aug 2004
    Posts
    42
    Are you sure that you have a one-to-one relationship between the rows in #A and #B. If you have multiple rows in #B that match a row in #A and some have the same value in col_2 as A then....

  3. #3
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66
    I used this kind of update a lot, and never had any problem. Worked even in 11.9.2. I'm often critical of Sybase, but the update-join is one of the more nice features, which takes the set-based paradigm one step further than Oracle.

    In addition to what Ted said: beware of NULL values. The '=' condition in the where clause won't be satisfied if you have nulls on both sides.

    And beware of the cardinality issue. Once, by mistake, I left an extra table name in the From list of a similar Update statement. That extra table was only growing by a few rows every day. A month later, the server was running out of log space. I suspect that internally, the Update writes to the log every change it needs to carry out, even if some changes affect the same records all over again.

    Andrew

  4. #4
    Join Date
    Jan 2004
    Posts
    51
    It looks to be strange that the update is not working, but as you said it could be because of 'A.' in set .

Posting Permissions

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