Post reply

what is the wrong with this update

  • July 25, 2007 at 8:37 pm

    #118003

    UPDATE studentmaster

       SET active = 0

      FROM studentdetail

     WHERE studentmaster .orderset_sys = studentdetail.orderset_sys

       AND order_text like  '%continuation of class%'and class_ID= ' 400000'

      

    Thanks in advance

  • July 25, 2007 at 8:48 pm

    #722467

    Probably space in studentmaster .orderset_sys

    _____________
    Code for TallyGenerator

  • July 26, 2007 at 10:51 am

    #722649

    If your display is challenging you visually, you could always copy your SQL into Word or equivalent editor, then show codes to highlight improper characters or empty spaces that don't belong. Even text editors that will show you the hexidecimal equivalents may help.

  • July 26, 2007 at 11:01 pm

    #722763

    There is a potential problem in this update... I've seen it lock up servers if parallism is spawned and the indexes are just right... if you need to do a join in an UPDATE, the target table MUST be included in the join (as with every joined update example in Books Online).  You should also get into the habit of using table aliases on every column in a join even if the column names are unique... I didn't add them to order_text or class_ID because I have no way of knowing which table they're actually in just by looking at this code.

    Also, you have a space in studentmaster .orderset_sys [Edit: I see Serqiy also pointed that out]

    UPDATE studentmaster

       SET active = 0

      FROM studentmast sm

              studentdetail sd

     WHERE sm.orderset_sys = sd.orderset_sys

       AND order_text like  '%continuation of class%'

       AND class_ID= ' 400000'

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 27, 2007 at 3:25 am

    #722808

    Hi,

    I have a query use it its working...........

     

    UPDATE SM  SET active = 0 

    FROM studentmaster SM Join studentdetail  SD On studentmaster .orderset_sys = studentdetail.orderset_sys WHERE 

    order_text like  '%continuation of class%'and class_ID= ' 400000'

     

    Thanks & Regrads

    Amit Gupta

     

  • July 27, 2007 at 4:45 am

    #722813

    Almost, Amit, but not quite. Your query will generate 2 errors:

    Server: Msg 107, Level 16, State 3, Line 1

    The column prefix 'studentmaster' does not match with a table name or alias name used in the query.

    Server: Msg 107, Level 16, State 1, Line 1

    The column prefix 'studentdetail' does not match with a table name or alias name used in the query.

    You meant to write this...

    UPDATE SM SET active = 0 
FROM studentmaster SM 
 INNER JOIN studentdetail SD ON SD.orderset_sys = SM.orderset_sys 
WHERE order_text LIKE '%continuation of class%' AND class_ID = ' 400000'

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply