$wpdb->update multiple rows, like IN in normal SQL

Question

I was wondering if it is possible to use $wpdb->update to update values for multiple rows like with IN in "normal" SQL.

I want to accomplish something like this example;

UPDATE [table]
SET [column_1] = [updated_value]
WHERE [column_2] IN ([comma_separated_ids])

I've been searching around for how this would work, but I'm yet to find another question asking this or any kind of answer/blog post explaining whether it can be done or not.

For now I'm using a query instead, but it would be nicer with a simple one liner.

Answer 1

As you can see in source code the = sign is hardcoded in the wpdb::update() method, so, by default, is not possible to use IN for update method.

Simplest way to do the trick is to use the wpdb::query() with your sql query, just be sure to properly escape all values

Example:

function wpdb_update_in( $table, $data, $where, $format = NULL, $where_format = NULL ) {

    global $wpdb;

    $table = esc_sql( $table );

    if( ! is_string( $table ) || ! isset( $wpdb->$table ) ) {
        return FALSE;
    }

    $i          = 0;
    $q          = "UPDATE " . $wpdb->$table . " SET ";
    $format     = array_values( (array) $format );
    $escaped    = array();

    foreach( (array) $data as $key => $value ) {
        $f         = isset( $format[$i] ) && in_array( $format[$i], array( '%s', '%d' ), TRUE ) ? $format[$i] : '%s';
        $escaped[] = esc_sql( $key ) . " = " . $wpdb->prepare( $f, $value );
        $i++;
    }

    $q         .= implode( $escaped, ', ' );
    $where      = (array) $where;
    $where_keys = array_keys( $where );
    $where_val  = (array) array_shift( $where );
    $q         .= " WHERE " . esc_sql( array_shift( $where_keys ) ) . ' IN (';

    if( ! in_array( $where_format, array('%s', '%d'), TRUE ) ) {
        $where_format = '%s';
    }

    $escaped = array();

    foreach( $where_val as $val ) {
        $escaped[] = $wpdb->prepare( $where_format, $val );
    }

    $q .= implode( $escaped, ', ' ) . ')';

    return $wpdb->query( $q );
}

Then use it like so:

wpdb_update_in(
  'posts', // table
  array( 'post_author' => '1', 'post_status' => 'draft' ), // data
  array( 'post_author' => array( '2', '3', '4', '5' ) ), // where
  array( '%d', '%s' ), // format
  '%d' // where format
);

The SQL performed will be

UPDATE wp_posts
SET post_author = 1, post_status = 'draft'
WHERE post_author IN (2, 3, 4, 5)