Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    2

    Question Unanswered: Problem with DELETE WHERE NOT IN

    Hi,

    Has anyone ever experienced problems with DELETE-queries that have a NOT IN operator in the WHERE-clause?

    I just fixed a bug that was caused by the following query:

    DELETE
    FROM sometable
    WHERE sometable.somecolumn NOT IN ('#valuelist#')

    It seemed as if the 'NOT IN' was treated as an 'IN', because the query did exactly the opposite from what it was supposed to. It deleted all records with corresponding values in the valuelist, rather than the other way around...

    Anyone? I'm curious if I should put this in my documentation as a known issue or not.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Problem with DELETE WHERE NOT IN

    Originally posted by pcp
    Hi,

    Has anyone ever experienced problems with DELETE-queries that have a NOT IN operator in the WHERE-clause?

    I just fixed a bug that was caused by the following query:

    DELETE
    FROM sometable
    WHERE sometable.somecolumn NOT IN ('#valuelist#')

    It seemed as if the 'NOT IN' was treated as an 'IN', because the query did exactly the opposite from what it was supposed to. It deleted all records with corresponding values in the valuelist, rather than the other way around...

    Anyone? I'm curious if I should put this in my documentation as a known issue or not.
    I have never seen that happen, and it did not happen when I just tried it.
    Can you post a simple example that demonstrates this happening?

  3. #3
    Join Date
    Jan 2003
    Posts
    2

    Re: Problem with DELETE WHERE NOT IN

    Thanks for your reply, Andrewst.

    At the moment I am very bussy with a project, but I will definitly try to find time within the next few days or so to post a simple example.

    The query that I had the problem with is part of a ColdFusion module which, I suppose, is not suitable for posting here

    Grtz

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the reason your query did "exactly the opposite" is quite likely because there was no column value that satisfied your condition, which was

    WHERE ... NOT IN ( 'something' )

    that's valid syntax, because inside the parentheses is a list of items, and in this case, the list happens to have just one item -- a character string

    now, i think what you want is

    WHERE ... NOT IN ( 38, 24, 36 )

    or alternatively

    WHERE ... NOT IN ( 'foo', 'bar', 'wow' )

    so what you have to do is adjust your query accordingly

    by the way, be careful using the name "valuelist" as your coldfusion variable, because that's also the name of the ValueList function used in query-of-query to change a column into a list of values

    rudy

Posting Permissions

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