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.
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');
use batch query for this
$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');
// Produces:
// UPDATE `mytable` SET `name` = CASE
// WHEN `title` = 'My title' THEN 'My Name 2'
// WHEN `title` = 'Another title' THEN 'Another Name 2'
// ELSE `name` END,
// `date` = CASE
// WHEN `title` = 'My title' THEN 'My date 2'
// WHEN `title` = 'Another title' THEN 'Another date 2'
// ELSE `date` END
// WHERE `title` IN ('My title','Another title')
for more info :- https://www.codeigniter.com/userguide2/database/active_record.html#update
the problem, quite likely, is that mysql_query only handles one query at a time. http://au2.php.net/mysql_query
Mysql_query() function can handle only one query at a time. So you can use
$this->db->update_batch('table_name', $data_array, 'unique_field');
If you are talking about mysql, then you can use mysqli::multi_query . This is basically used to executes one or multiple queries which are concatenated by a semicolon.
