Search code examples
mysqlsqlcodeigniteractiverecordcodeigniter-2

joined UPDATE/INSERT in CI active records

I just saw this on my office mate that you can Update using joins? My Question is can you also do this with Codeigniter's active record? given the following SQL query:

UPDATE tbl1 JOIN tbl2 ON tbl1.id = tbl2.tbl1_id  
SET tbl1.column2 = "value1", tbl2.column3 = "value1"
WHERE tbl1.column1 = 1;

lastly, are there any specific version of mysql that I need to use for me to execute this? thanks!

Solution

  • MySQL with version >3.23 supports joined-table UPDATE statements. You can do it with CI:

    $this->db->set('tbl1.column2', 'value1');
$this->db->set('tbl2.column3', 'value2');
$this->db->where('tbl1.column1', 1);
$this->db->update('tbl1 JOIN tbl2 ON tbl1.id = tbl2.tbl1_id');