Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Update Set question

    OK...so Why doesn't the multiple updates get applied to the last row?


    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 varchar(8000))
    GO
    INSERT INTO myTable99(Col1)
    SELECT '1~2~3~4~5' UNION ALL
    SELECT '1~2~3~4~5' UNION ALL
    SELECT '1,2~3,4,5'
    GO
    
    CREATE TABLE myTable00(Old varchar(10),New varchar(10))
    GO
    
    INSERT INTO myTable00(Old,New)
    SELECT '~', '' UNION ALL
    SELECT ',',''
    GO
    
    SELECT * FROM myTable99
    
        UPDATE a SET Col1 = REPLACE(Col1,Old,New)
          FROM myTable99 a 
    INNER JOIN myTable00 b
    	ON a.Col1 LIKE '%'+Old+'%'
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable00
    DROP TABLE myTable99
    GO


    any ideas?

    Is it like SELECT @x = col from table

    Where there is more than 1 row in the rs
    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.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah, now there's a very good question!

    According to the SQL-92 specification (and I think the SQL-89 standard too, but I'm not sure), SQL UPDATE statements will process a "before" value to an "after" value for updated columns, which is why standard SQL doesn't permit JOIN operations of any kind in an UPDATE although it does allow sub-selects. The JOIN might force multiple operations on a single row (like yours does), which violates the standard, while a sub-select can't cause this problem. In order to comply with the standard, Microsoft changed the processing of the UDPATE statement over the howls of many developers whose code that change broke.

    -PatP

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

    Thanks a bunch...that's great...


    I'm actually cracking up right now...

    When did this "feature" work like that before?

    Broke down and turned it in to a cursor.....
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by Brett Kaiser
    When did this "feature" work like that before?
    Back in the olden days, we used ta walk 20 km to school and back home, uphill both ways!

    The problem in SQL 4.21a was that there wasn't a defined way to process JOIN within UPDATE loops. Depending on how complex your JOIN was, what was in cache, and the mood of the server, it might appear to apply one, many, or all of the possible combinations in whatever order it chose. The results weren't completely predictable even from run to run on the same server!

    That is part of why X3H2 decided to codify what behavior was expected of UPDATE statements, and why they chose the lowest common denominator for JOIN processing to ensure that every SQL vendor had equal opportunity to implement it correctly.

    SQL 6.0 and SQL 6.5 more or less left the behavior alone, mostly because they had other fish to fry, but also because nobody else had rewritten their code to meet the new standard yet. When SQL 7.0 came out, Microsoft made a concerted effort to meet every existing (and reasonable) standard they could find, so that is when this one snuck up and bit a lot of people who had relied on an undocumented feature.

    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    80

    Re: Update Set question

    Thanks brett,
    good catch.U made me to check all the triggers in which I have
    simmilar updates.
    but for the one here in this code u can repeat the updates which will serve the purpose.

    Code:
    while @@rowcount>0
        UPDATE a SET Col1 = REPLACE(Col1,Old,New)
          FROM myTable99 a 
          INNER JOIN myTable00 b
    	ON a.Col1 LIKE '%'+Old+'%'

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

    I thought about that...

    But I figured I'd reincurr a scan everytime...

    Thinking about it now....I'm doing that anyway with a Cursor...

    and icurring more overhead...


    opinons?
    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
    You could make a scan through table00 to build a nested set of Replace calls, then make a single pass through table99 to dynamically apply them. That should drastically improve efficiency since you'd only have the overhead of building the list from table00 once, then the application of the expression against table99 once.

    -PatP

Posting Permissions

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