Post reply

Looking at someone elses code

  • December 21, 2009 at 1:34 pm

    #137748

    Never seen this type of from statement before. Does anyone use it and know if it's exactly like an inner join?

    THANKS!!!

  • December 21, 2009 at 1:40 pm

    #1095077

    it is an inner join. shoot how embarrasing.

    i remember seeing this in my text book way back in my college days

    ex: from table1, table2 where table1.id = table2.id

  • December 21, 2009 at 1:45 pm

    #1095081

    Yup, that's the old-style join syntax. I'd recommend converting it to the ANSI join syntax for readability.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • December 21, 2009 at 1:47 pm

    #1095085

  • December 21, 2009 at 1:54 pm

    #1095095

    SQL Iron Chef (12/21/2009)

    You do know that the NOLOCK hint on the update is ignored, right?

  • December 21, 2009 at 1:58 pm

    #1095098

    Lynn Pettis (12/21/2009)

    SQL Iron Chef (12/21/2009)

    You do know that the NOLOCK hint on the update is ignored, right?

    Actually I didn't! I was just trying to digest someone else code, that was just the small piece of it. I'm actually kind of paranoid about using no locks because it might give inaccurate results? But I do know its faster.

  • December 21, 2009 at 2:03 pm

    #1095104

    SQL Iron Chef (12/21/2009)

    Lynn Pettis (12/21/2009)

    SQL Iron Chef (12/21/2009)

    You do know that the NOLOCK hint on the update is ignored, right?

    Actually I didn't! I was just trying to digest someone else code, that was just the small piece of it. I'm actually kind of paranoid about using no locks because it might give inaccurate results? But I do know its faster.

    I'd be more concerned, this looks like a correlated subquery.

  • December 21, 2009 at 4:00 pm

    #1095152

    Hey Iron Chef, did you remove the query? I see no query in any of your posts. Just curious.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 21, 2009 at 4:20 pm

    #1095164

    Lynn Pettis (12/21/2009)

    SQL Iron Chef (12/21/2009)

    Lynn Pettis (12/21/2009)

    SQL Iron Chef (12/21/2009)

    You do know that the NOLOCK hint on the update is ignored, right?

    Actually I didn't! I was just trying to digest someone else code, that was just the small piece of it. I'm actually kind of paranoid about using no locks because it might give inaccurate results? But I do know its faster.

    I'd be more concerned, this looks like a correlated subquery.

    And, it does not have a where clause to limit the rows being updated. So, every row in #tmpInventory will be updated.

    Oops, even worse - the join is a cartesian product, so no telling what that update is going to actually update and how many times.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • December 21, 2009 at 4:47 pm

    #1095176

    Jeffrey Williams-493691 (12/21/2009)

    Lynn Pettis (12/21/2009)

    SQL Iron Chef (12/21/2009)

    Lynn Pettis (12/21/2009)

    SQL Iron Chef (12/21/2009)

    You do know that the NOLOCK hint on the update is ignored, right?

    Actually I didn't! I was just trying to digest someone else code, that was just the small piece of it. I'm actually kind of paranoid about using no locks because it might give inaccurate results? But I do know its faster.

    I'd be more concerned, this looks like a correlated subquery.

    And, it does not have a where clause to limit the rows being updated. So, every row in #tmpInventory will be updated.

    Oops, even worse - the join is a cartesian product, so no telling what that update is going to actually update and how many times.

    OK Jeff, what am I missing here - I see no code at all.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 21, 2009 at 5:07 pm

    #1095182

    CirquedeSQLeil (12/21/2009)

    OK Jeff, what am I missing here - I see no code at all.

    Well - you're probably missing the ability to access the linked image. The code is an image at:

    http://i673.photobucket.com/albums/vv100/sqlpuzzles/from2.jpg

    I am guessing that this site is blocked for you.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • December 21, 2009 at 5:12 pm

    #1095184

    Jason Jeffery is right, you don't have a WHERE clause to join the two tables in the second FROM clause (the UPDATE statements FROM clause). Are you sure you copied all the code into your post?

    Edit: Correct name reference.

  • December 21, 2009 at 5:12 pm

    #1095185

    Jeffrey Williams-493691 (12/21/2009)

    CirquedeSQLeil (12/21/2009)

    OK Jeff, what am I missing here - I see no code at all.

    Well - you're probably missing the ability to access the linked image. The code is an image at:

    http://i673.photobucket.com/albums/vv100/sqlpuzzles/from2.jpg

    I am guessing that this site is blocked for you.

    OK. That was precisely it. Thank you websense. Not until I loaded up the url, did i get any hint that I was being blocked.

    EDIT: And I thought you guys were psychic :hehe:

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 21, 2009 at 5:13 pm

    #1095186

    Lynn Pettis (12/21/2009)

    Jason is right, you don't have a WHERE clause to join the two tables in the second FROM clause (the UPDATE statements FROM clause). Are you sure you copied all the code into your post?

    Err...That was Jeff.

    Jason is blocked from seeing the pic.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 21, 2009 at 5:13 pm

    #1095187

    thanks for the info guys.

    basically i was given a task to rewrite that store procedure and was trying to figure out what the sp was doing since i didn't write the version in the pic i posted.

    it was much longer but i just cut the piece that i didnt understand

Viewing 15 posts - 1 through 15 (of 16 total)

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