title: MYSQL: using ON DUPLICATE KEY UPDATE + COALESCE
body: <p>I have written below stored procedure which inserts records for particular date. If the date is duplicate then should update the values. But while inserting a new record for same date, if any of the value is null, it is making already inserted values for that date null as well.
  My Stored Procedure is:</p>
  
  <pre><code>CREATE PROCEDURE sp_setrecords (IN daten date,IN daystart time,IN lunchstart time,IN lunchend time,IN breakstart time,IN breakend time,IN dayend time,IN casualleave time,IN sickleave time,IN holidays time,IN leavewithoutpay time)
      BEGIN
          insert into dailyrecord (date,daystart,lunchstart,lunchend,breakstart,breakend,dayend,casualleave,sickleave,holidays,leavewithoutpay) values              (daten,daystart,lunchstart,lunchend,breakstart,breakend,dayend,casualleave,sickleave,holidays,leavewithoutpay) 
          ON DUPLICATE KEY UPDATE 
          date = COALESCE(VALUES(date)),
          daystart = COALESCE(VALUES(daystart)),
          lunchstart = COALESCE(VALUES(lunchstart)),
          lunchend = COALESCE(VALUES(lunchend)), 
          breakstart = COALESCE(VALUES(breakstart)), 
          breakend = COALESCE(VALUES(breakend)),
          dayend = COALESCE(VALUES(dayend)),
          casualleave = COALESCE(VALUES(casualleave)), 
          sickleave = COALESCE(VALUES(sickleave)), 
          holidays = COALESCE(VALUES(holidays)),
          leavewithoutpay = COALESCE(VALUES(leavewithoutpay));
      END
  </code></pre>
  
  <p>Example: initial values are:
  "2018-01-02","9:30:00",null,null,null,null,null,null,null,null,null"</p>
  
  <p>then if i insert 13:30:30 for same date at second position, it returns:</p>
  
  <p>"2018-01-02",null,"13:30:00",null,null,null,null,null,null,null,null"</p>
  
  <p>How can I update the value only if it is not null?</p>