$wpdb won’t insert NULL into table column

by

Update:

Since WordPress 4.4. this is now supported by the insert, update, replace and delete methods of wpdb and the ticket #15158 has been closed as fixed.

Thanks to @dmsnell for commenting about that update.

On the other hand, the null support in wpdb::prepare() is currently closed as wontfix in ticket #12819.

Previous answer:

NULL not supported:

It looks like you will have to write your own custom SQL to update the value with NULL.

Currently NULL is not supported by $wpdb->prepare(), that takes the input through the vsprintf formatting function.

Check out these open Trac tickets:

These tickets are about 4 years old, so I wouldn’t hold my breath until this gets supported by the core 😉

You should take a look at the source as @s_ha_dum suggested.

A possible workaround:

If you’re adventurous you can try the following with the query filter:

    // Add a filter to replace the 'NULL' string with NULL
    add_filter( 'query', 'wpse_143405_query' );

    global $wpdb;
    $wpdb->update(
        'table',
        array( 
            'status' => 'NULL',
        ), 
        array( 'id' => 1 ) 
    );

    // Remove the filter again:
    remove_filter( 'query', 'wpse_143405_query' );

where

/**
 * Replace the 'NULL' string with NULL
 * 
 * @param  string $query
 * @return string $query
 */

function wpse_143405_query( $query )
{
    return str_ireplace( "'NULL'", "NULL", $query ); 
}

You might want to use a more unique string than 'NULL' to replace, perhaps '###NULL###' instead.

Leave a Comment