How to perform UPDATE with multiple table JOINs in CodeIgniter

Data and Databases
1

I’m having trouble creating an update query that uses multiple joins in my CodeIgniter model. The query works fine without joins, but when I try to join several tables together and then update, it fails to execute properly. I need to connect at least 3 tables but I’m stuck on making this work.

Here’s what I’m attempting:

$update_data = array(
    'request_status' => 'approved',
    'category_code' => 'RT',
);

$this->db->join('request_categories', 'request_categories.category_id = service_requests.category_id')
->join('staff_members', 'staff_members.staff_id = service_requests.assigned_staff')
->set($update_data)
->where('request_id', $request_number)
->update('request_categories','service_requests');

The error suggests there might be an issue with how I’m structuring the update statement with multiple joins. Has anyone successfully implemented this type of query in CodeIgniter?

2

Your update method call is incorrect - you can’t pass two table names like that. CodeIgniter only requires the primary table you’re updating, even with joins.

Here’s the corrected approach:

$update_data = array(
    'service_requests.request_status' => 'approved',
    'service_requests.category_code' => 'RT',
);

$this->db->join('request_categories', 'request_categories.category_id = service_requests.category_id')
->join('staff_members', 'staff_members.staff_id = service_requests.assigned_staff')
->set($update_data)
->where('service_requests.request_id', $request_number)
->update('service_requests');

By prefixing the column names with the table name to avoid conflicts and removing that second table parameter, the query will execute correctly. This method works well for updates involving joined tables, and I’ve successfully implemented it multiple times.

3

actually, i think it’s simpler than that - codeigniter’s update() method gets weird with joins. i’d just use query() instead for complex updates. something like $this->db->query("UPDATE service_requests sr JOIN request_categories rc ON rc.category_id = sr.category_id SET sr.request_status = 'approved' WHERE sr.request_id = ?", [$request_number]); works way better in my experience.

4

Hmm, interesting. Are you actually updating fields in multiple tables or just the main one? If you’re only updating service_requests, those joins might be unnecessary overhead. What’s the specific error message you’re getting?