SQL Server Performance

"Update" statement syntax

Discussion in 'General Developer Questions' started by eramgarden, Sep 20, 2006.

  1. eramgarden New Member

    I like to know if this is wrong before i open my mouth to my coworker [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />He has an Update statment like this :<br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Update <b>TB1</b> set whatever = TB1.xxx + TB2.xxx <br /> From <b>TB2 Inner Join TB3</b> <br /> ON (TB2 and TB3 fields) <br /> INNER Join <b>TB4</b> <br /> ON (<b>TB1</b> and TB4 fields) <br />Where (<b>TB1</b> fields = TB2 fields) <br /><br /><br /></font id="code"></pre id="code"><br /><br /><br /><br /><b>He does NOT have TB1 referenced in the joins.</b> He says the update works. I think the syntax should be like this with TB1 actually referenced. This is sql server2000:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />UPDATE TB1 <br />SET whatever = TB1.x + TB2.y <br />From <b>TB1</b> INNER JOIN <b>TB2</b> ON TB2.xxx = TB1.xxx --<b>TB1 is referenced</b><br /> INNER JOIN <b>TB3</b> ON TB3.yyy = TB2.yyy <br /> INNER JOIN <b>Tb4</b> ON TB4.zzz = TB1.zzz<br /><br /></font id="code"></pre id="code">
  2. harsh_athalye New Member

    Although syntactically there is no error, the result will be unpredictable for first statement where TB1 is not referenced. Second one is correct way to do it !

    "Nothing is Impossible"
  3. eramgarden New Member

    would his version create unpredictable results since the main table is not mentioned in the joins?
  4. Bredsox New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by eramgarden</i><br /><br />I like to know if this is wrong before i open my mouth to my coworker [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />He has an Update statment like this :<br /><pre id="code"><font face="courier" size="2" id="code"><br /><br />Update <b>TB1</b> set whatever = TB1.xxx + TB2.xxx <br /> From <b>TB2 Inner Join TB3</b> <br /> ON (TB2 and TB3 fields) <br /> INNER Join <b>TB4</b> <br /> ON (<b>TB1</b> and TB4 fields) <br />Where (<b>TB1</b> fields = TB2 fields) <br /><br /><br /></font id="code"></pre id="code"><br /><br /><br /><br /><b>He does NOT have TB1 referenced in the joins.</b> He says the update works. I think the syntax should be like this with TB1 actually referenced. This is sql server2000:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />UPDATE TB1 <br />SET whatever = TB1.x + TB2.y <br />From <b>TB1</b> INNER JOIN <b>TB2</b> ON TB2.xxx = TB1.xxx --<b>TB1 is referenced</b><br /> INNER JOIN <b>TB3</b> ON TB3.yyy = TB2.yyy <br /> INNER JOIN <b>Tb4</b> ON TB4.zzz = TB1.zzz<br /><br /></font id="code"></pre id="code"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br />I don't think you can assure that the update will work for TB1 when you don't have table referenced. What is TB1 referring to? Doesn't make a lot of sense to me. <br />
  5. eramgarden New Member


    Wonder if his causes "cartesian-join" and then his "where" clause:Where (TB1 fields = TB2 fields)

    takes care of the join. But yeah, the syntax isnt intuitive.
  6. Bredsox New Member

    It's kinda funny that eramgarden always refers to his coworker for most of the questions. Looks like you guys don't get along very well. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />][<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  7. eramgarden New Member

    LOL, he's new at SQL Server stuff. This is his first stored proc. I gave him some ideas but he's not willing to accept my critique. So I have to make sure what i'm talking about before i can convince him.<br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />] Honestly, i have a coworker who doesnt know what he's doing [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  8. Bredsox New Member

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />]

Share This Page