steve steve - 1 month ago 7
MySQL Question

active record omit insert when value exists

I normally parse a data array to the db obj as follows

$this->db->update('myTable', array('col1' => "foo", 'col2' => 'bar' ,'col3' => 'abc'), array('id' => $id));


Is there a way of only updating the columns that have a null value? Currently i'm having to run a query first and then loop through. Not very efficient.

Answer

Here are a couple of solutions:

$sql = 'UPDATE myTable SET col1 = IF(col1 IS NULL, ?, col1), col2 = IF(col2 IS NULL, ?, col2), col3 = IF(col3 IS NULL, ?, col3)';
$this->db->query($sql, ['foo', 'bar', 'abc']);

or

$this->db->set('col1', 'IF(col1 IS NULL, "foo", col1)', FALSE)
      ->set('col2', 'IF(col2 IS NULL, "bar", col2)', FALSE)
      ->set('col3', 'IF(col3 IS NULL, "abc", col3)', FALSE)
      ->update('myTable');