Search code examples
javasqlibatisderby

Is NULL not allowed is SQL delete statement (for Derby)?

Does anyone know why the following query fails in Derby?

delete from MyTable
where
((null = null) or (col1 = null)) OR
((102 = null) or (col2 = 102)))

I get the following error:

Error: Syntax error: Encountered "null" at line 3, column 3.
SQLState:  42X01
ErrorCode: -1

The sql is being generated in a java program based on SQL I've written into an iBatis ORM layer config. The prepared statement is as follows:

delete from MyTable
where
((? = null) or (col1 = ?)) OR
((? = null) or (col2 = ?)))

As you can see what I'm trying to say is that if the parameter is not null then test the column value against the parameter

Solution

  • I believe what you want is:

    DELETE FROM `name_of_table` WHERE `name_of_column` IS NULL

    SQL has this really weird property where you can use =value with most values, except NULL.