Codeigniter - database: how to update multiple tables with a single update request

Question

I saw this one on the codeigniter forum

Given the code below

UPDATE a INNER JOIN b USING (id) SET a.firstname='Pekka', a.lastname='Kuronen', b.companyname='Suomi Oy',b.companyaddress='Mannerheimtie 123, Helsinki Suomi' WHERE a.id=1; 

Here's how you are likely to do it in Codeigniter

 $this->db->set('a.firstname', 'Pekka'); $this->db->set('a.lastname', 'Kuronen'); $this->db->set('b.companyname', 'Suomi Oy'); $this->db->set('b.companyaddress', 'Mannerheimtie 123, Helsinki Suomi'); $this->db->where('a.id', 1); $this->db->join('table2 as b', 'a.id = b.id'); $this->db->update('table as a'); 

this does not really work. I looked at the SQL that this produces, and the results do not even mention the join.

Does anyone know how to do an update using a connection using the Active Record Database Classignign?

Answer 1

One solution that I found is to remove the whole connection and move the join condition to the where function, you will also need to change the update line to include a new table.

 $this->db->set('a.firstname', 'Pekka'); $this->db->set('a.lastname', 'Kuronen'); $this->db->set('b.companyname', 'Suomi Oy'); $this->db->set('b.companyaddress', 'Mannerheimtie 123, Helsinki Suomi'); $this->db->where('a.id', 1); $this->db->where('a.id = b.id'); $this->db->update('table as a, table2 as b'); 

Answer 2

Using two separate queries in a transaction should solve your problem. If the request fails, the other returns.