Cannot execute multiple update queries in one statement [duplicate]

Question

I want to update multiple rows in one statement. I am using CodeIgniter framework. My code is given below.

$query = "update students set id = 12 where id = 10; update students set id = 13 where id = 11;";
$this->db->query($query);

But it is giving me error, saying I have a syntax error near 'update students set id = 13 where id = 11;' I don't know what I am doing wrong.

Answer 1

You can use codeigniter Active record batch update :

$data = array(
   array(
      'title' => 'My title' ,
      'name' => 'My Name 2' ,
      'date' => 'My date 2'
   ),
   array(
      'title' => 'Another title' ,
      'name' => 'Another Name 2' ,
      'date' => 'Another date 2'
   )
);

$this->db->update_batch('mytable', $data, 'title');