How to implement UPDATE with JOIN using CodeIgniter Query Builder

Data and Databases
1

I’m having trouble converting a MySQL UPDATE query with JOIN to CodeIgniter’s Active Record format. Here’s my original SQL that works perfectly in phpMyAdmin:

UPDATE
    user_profiles 
INNER JOIN user_settings ON user_profiles.profile_id = user_settings.profile_ref
SET
    user_profiles.status = 0,
    user_settings.status = 0
WHERE
    user_profiles.user_id = 10

I’ve been trying to convert this to CodeIgniter’s query builder but can’t get it working. Here’s what I attempted:

$this->db->set('up.status', 0);
$this->db->set('us.status', 0); 
$this->db->where('up.user_id', $user_id);    
$this->db->update(USER_PROFILES_TABLE." as up");  
$this->db->join(USER_SETTINGS_TABLE." as us", 'up.profile_id=us.profile_ref'); 
if($this->db->affected_rows() > 0) { 
    return true;
} else { 
    return false; 
}

The query executes but doesn’t update any records. What’s the correct way to structure an UPDATE with JOIN in CodeIgniter? Any help would be appreciated.

2

Your join() needs to come before update(), not after. CodeIgniter builds the SQL sequentially, so putting join after update makes it useless.

Here’s the fix:

$this->db->set('up.status', 0);
$this->db->set('us.status', 0);
$this->db->join(USER_SETTINGS_TABLE." as us", 'up.profile_id=us.profile_ref');
$this->db->where('up.user_id', $user_id);
$this->db->update(USER_PROFILES_TABLE." as up");

I hit this exact same issue months back when migrating old queries. CodeIgniter processes each method call in order, building the SQL piece by piece. Call update() first and it locks in the query structure before it even sees your join().

3

just hit this yesterday! codeigniter’s query builder sucks at multi-table updates. I ditched it and used raw sql with $this->db->query() instead. way easier than wrestling with active record’s limitations - worked perfectly for my join update.

4

hold up - does codeigniter’s query builder actually support multi-table updates? i’ve wondered about this but never tested it. are you sure both tables are getting updated and not just the primary one? try echoing $this->db->last_query() to see what sql it’s actually generating.