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

Question

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.

Answer 1

$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