6

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.

  • 1
    There is no reason for it not to work. – Mark Kaplun Aug 1 '14 at 14:18
  • 1
    But HOW? I tried adding a comma separated string as value in the where array, but it only updated the first id. – Ludvig Sjöbeck Aug 1 '14 at 14:54
  • hmm, I see that people with better knowledge then me answered it ;) – Mark Kaplun Aug 1 '14 at 15:41
10

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)
|improve this answer|||||
  • I haven't worked that much in WP, so I was unaware of the source code. I'll make sure to bookmark it for future reference. I like your solution and I'll mark as the answer. – Ludvig Sjöbeck Aug 4 '14 at 7:13
  • Hard coded wordpress and hard to be used... – Vishal Kumar Sahu Sep 10 '17 at 22:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.