[SOLVED] Convert raw UPDATE SQL containing CASE statements in the SET clause into CodeIgniter query builder methods

Convert raw UPDATE SQL containing CASE statements in the SET clause into CodeIgniter query builder methods

Can anybody convert the normal mysql query into codeigniter update strcuture.

UPDATE game_rounds 
SET from_score = CASE WHEN from_id = 2 THEN 65 ELSE from_score END,
    to_score = CASE WHEN to_id = 2 THEN 65 ELSE to_score END 
WHERE round_id=5

I tried update_batch(), but I'm not able to find the correct solution.

Solution

$this->db
    ->set('from_score', 'CASE WHEN from_id = 2 THEN 65 ELSE from_score END', FALSE)
    ->set('to_score', 'CASE WHEN to_id = 2 THEN 65 ELSE to_score END', FALSE)
    ->where('round_id', 5)
    ->update('game_rounds');

$this->db->set() enables you to set values for inserts or updates. It can be used instead of passing a data array directly to the insert or update functions.

The optional third parameter ($escape), that will prevent data from being escaped if set to FALSE.

CI Active Record Class