Update SQL when where clause condition can be null

Question

I have an update SQL statement to update a row

   UPDATE COM_TRANSACTION_LOGS    
        SET END_TIME  = ?, 
        RESPONSE   = ?  
   WHERE   
   TRANSACTION_ID  = ?   
   AND    
   MESSAGE_ID  = ?  

The problem here is that the MESSAGE_ID can be null in some cases so the update SQL comes as

  [DEBUG] {pstm-100101} Parameters:[2013-05-14 10:38:01.485, XML, 123XYZAAA1236511, null]

This fails to update because the where clause becomes like

WHERE   
   TRANSACTION_ID  = '123XYZAAA1236511'
   AND    
   MESSAGE_ID  = null

How can I compare against null value through prepared statement.

I know this where clause for null comparison has to be like this

   WHERE   
     TRANSACTION_ID  = '123XYZAAA1236511'
   AND    
      MESSAGE_ID  is null

How can I tell my prepared statement to set the where clause as is null without using two queries and using them conditionally in case of null or ='somevalue

Answer 1

You can replace this line:

MESSAGE_ID  = ?  

By this:

COALESCE(MESSAGE_ID,-1) = COALESCE(?,-1)

Answer 2

In Oracle there is an NVL statement for accomplishing this (nvl(?, value_to_replace_null)). I'm not sure what variant of SQL you're using, but there is probably something similar. You might also use IFNULL, ISNULL, and COALESCE for SQL Server and MySQL.