Update randomly certain number of rows

Question

I'm trying to update a certain number of rows of my entity "Vehicule". I have no idea how could it work. I'm actually trying to modify only two rows where direction= 5.This is the function I used in order to update.

 public function ValidAction(\OC\UserBundle\Entity\User $direction) {

    $qb = $this->getDoctrine()
        ->getRepository('CarPfeBundle:Vehicule')
        ->createQueryBuilder('v');

    $q = $qb->update ('CarPfeBundle:vehicule v')
            ->set('v.direction', '?1')
            ->where('v.direction = ?2')
            ->setParameter(1, $direction)
            ->setParameter(2, 5)
            ->getQuery();

        $p = $q->execute();

    return $this->redirect($this->generateUrl('demandeveh_afficher'));
}

But the above code update all rows of my database. I need to update only two rows. Any help please?

rows that I want to update? Actually it's random I only need to update any two random rows which are adequate with the where statement. I've been trying to use "update top(2).." but it's not working — LSoft, Apr 24 '16 at 10:44
If that, try to find randomly any 2 Vehicule : $q = $qb->find($vehiculeId); and modify these Vehicule — Houssem ZITOUN, Apr 24 '16 at 10:55

Houssem ZITOUN · Answer 1 · 2016-04-24T12:52:58.320

Try to do this ;

public function ValidAction(\OC\UserBundle\Entity\User $direction) {

    $qb = $this->getDoctrine()
              ->getRepository('CarPfeBundle:Vehicule')
              ->createQueryBuilder('v');

    // $ids an array that contains all ids with your condition
     $ids = $qb->select('v.id')
               ->where('v.direction = :direction')
               ->setParameter(
                     array(
                         'direction' => $direction
                    )
                )
               ->getQuery()
               ->getResult();

    $id1 = $ids[array_rand($ids)];
    $id2 = $ids[array_rand($ids)];

    //To be sure that $id1 is different from id2
    while ($id1 == $id2) {
        $id2 = $ids[array_rand($ids)];
    }


    $q = $qb->update ('CarPfeBundle:vehicule v')
            ->set('v.direction', ':val1')
            ->where('v.direction = :val2')
            ->andWhere('v.id IN (:id1, :id2)')
            ->setParameter(
                 array(
                    'val1' => $direction ,
                    'val2' => 5 ,
                    'id1'  => $id1,
                    'id2'  => $id2,
                 )
              )
            ->getQuery();

   $p = $q->execute();

return $this->redirect($this->generateUrl('demandeveh_afficher'));

}

With the above code I hope you can update only two rows and randomly.

Good luck !

score 0 · Answer 2 · edited May 23 '17 at 11:46

While a solution like Houssem Zitoun suggested may work, why not use a subquery?

If you get the (like I did, if not, just skip the middle SELECT)

Error: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

go with this answer and something like (doc): - untested

UPDATE CarPfeBundle:Vehicule v
  SET v.direction = ?1
  WHERE v.direction IN
    (SELECT * FROM (
      SELECT v.direction
      FROM CarPfeBundle:Vehicule v2
      WHERE v.direction = ?2 LIMIT 2
    )) AS sq

Update randomly certain number of rows

2 Answers2