Wordpress - $wpdb won't insert NULL into table column

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:

• wpdb::prepare support for null

• wpdb insert & update with null values

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.

wpdb->update defaults to a string for all data types.

format
(array|string) (optional) An array of formats to be mapped to each of the value in $data. If string, that format will be used for all of the values in $data. If omitted, all values in $data will be treated as strings unless otherwise specified in wpdb::$field_types.

http://codex.wordpress.org/Class_Reference/wpdb#UPDATE_rows

You can specify a format but the allowable specifiers are:

Possible format values: %s as string; %d as integer (whole number) and %f as float. (See below for more information.) If omitted, all values in $where will be treated as strings.

http://codex.wordpress.org/Class_Reference/wpdb#UPDATE_rows

You can read through the source and work out the process.

If you hack the wpdb->prepare method (on a dev server that gets wiped clean periodically :) ) to dump the SQL before just before the return, you will see that the replacement happens before wpdb->prepare:

string(48) "UPDATE `table` SET `status` = %s WHERE `id` = %s"

Though, as suggested by @birgire, it may well be a limit to prepare that prompted that replacement.

I'd like to further explain how to do this in WP 4.4 and beyond. You need to set both the data and format element that you wish to be null to a PHP 'null' value.

The example in ticket #15158 is as follows:

$wpdb->update($ttable, 
              [
                'user_id' => NULL,
                'status' => 'available',
                'update_time' => $now->format('Y-m-d H:i:s')
              ], [
                'therapist_id' => $therapist_id,
                'user_id' => $user_id,
                'start_time' => $ub['start_time']
              ], [
                 NULL,
                 '%s',
                 '%s'
              ], [
                 '%d',
                 '%d',
                 '%s'
            ]);