Results 1 to 3 of 3

Thread: ADT Upate SQL

  1. #1
    Join Date
    Jan 2008
    Posts
    25

    Unanswered: ADT Upate SQL

    Guys

    I am completely new to this SQL - so bear with me!

    At the moment I have a set of 30 files that simply updates two fields, but the where criteria is slightly different (ie., in the example below the cdocode changes).

    I have tried to create one SQL that I can run that will update effectively using the or statement, example below:


    UPDATE cdofile
    SET status = ‘C’, statusnote = ‘autoclosure’
    WHERE status = ‘N’ and cdocode=’10’ and email is null
    Or status =’N’ and cdocode=’15’ and email is null

    The issue is that I have 30 "or" lines and when the query is validated, I get "success", but when I run the query, it just never finishes and updates, it seems to hang without completing.

    Does anyone have any suggestions on using the "or" or infact, the script itself?

    Thanks in advance for any help !

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Operators have the following precedence from highest to lowest:

    unary (single argument) – + ~
    * / %
    binary (two argument) + – & | ^
    not
    and
    or

    Operators at the same level execute left to right
    You can change the order of execution with parentheses

    So your where clause is the same as
    WHERE (status = ‘N’ and cdocode=’10’ and email is null
    and cdocode=’15’ and email is null) Or status =’N’

    I think you want
    WHERE status = ‘N’
    and email is null
    and cdocode in ('10','15')

    What indexes exists?

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Operators have the following precedence from highest to lowest:

    unary (single argument) – + ~
    * / %
    binary (two argument) + – & | ^
    not
    and
    or

    Operators at the same level execute left to right
    You can change the order of execution with parentheses

    So your where clause is the same as
    WHERE (status = ‘N’ and cdocode=’10’ and email is null
    and cdocode=’15’ and email is null) Or status =’N’

    I think you want
    WHERE status = ‘N’
    and email is null
    and cdocode in ('10','15')

    What indexes exists?

Posting Permissions

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