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?

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

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