use mysql variable in a $wpdb->query to reindex a column?

Question

I have this MySQL query, which do work in PHPMyAdmin:

set @ROW = 0;UPDATE `wp_wpsstm_subtracks` SET `subtrack_order` = @ROW := @ROW+1 WHERE tracklist_id='176226' ORDER BY `subtrack_time` ASC

I would like to use it within Wordpress:

    $querystr = $wpdb->prepare("set @ROW = 0;UPDATE `$subtracks_table` SET `subtrack_order` = @ROW := @ROW+1 WHERE tracklist_id='%d' ORDER BY `subtrack_time` ASC", $this->post_id );
    return $wpdb->query($querystr);

But it fires a syntax error:

WordPress database error Erreur de syntaxe près de 'UPDATE wp_wpsstm_subtracks SET subtrack_order = @ROW := @ROW+1 WHERE trackli' à la ligne 1 for query set @ROW = 0;UPDATE wp_wpsstm_subtracks SET subtrack_order = @ROW := @ROW+1 WHERE tracklist_id='176226' ORDER BY subtrack_time ASC

How can I make this work ?

Thanks !

Answer 1

If I correctly understood, this is not possible (two queries instead of one). I ended up with this sad and time-consuming loop... :/

    //get subtracks
    $querystr = $wpdb->prepare("SELECT subtrack_id FROM `$subtracks_table` WHERE tracklist_id='%d' ORDER BY `subtrack_time` ASC",$this->post_id);
    if ( !$ids = $wpdb->get_col($querystr) ) return;

    //update order
    $i = 0;
    foreach($ids as $id){

        $i++;

        $wpdb->update( 
            $subtracks_table, //table
            array('subtrack_order'=>$i), //data
            array('subtrack_id'=>$id) //where
        );
    }

Answer 2

I have just created this and it is working like a charm:

$wpdb->query(
    "UPDATE " . $table . " SET your_column_name = (
        SELECT (@rownum := @rownum + 1) FROM (SELECT @rownum := 0) r
    ) 
    WHERE some_column = " . $id . " AND some_other_column = " . $some_value
);

You can remove additional term AND if you don't need it.