title: MySQL update if exists else insert
body: <p>I have a table of archived values for each channel_id, but I also want to keep the current value for each channel_id available. I first tried to put it in a separate table, but using a union on the tables for each select query was impossibly slow (I made a view of the union of the two tables). </p>
  
  <p>So I guess the best alternative is to keep it in the table of archived data, with an extra column to indicate if it is the current value? </p>
  
  <p>My question is then, how do I add the current value to that table if a row does not exist with the same channel_id and current column equal to 1, and update it if it does exist? </p>
  
  <p>I can't see a way to make a unique index that references both channel_id and current, since many channel_ids will have current = 0. So replace won't work. And update won't always work, because the row may not exist yet. </p>
  
  <p>Is there a better solution? The table will have millions of entries and there will be hundreds of thousands of channel_ids.</p>
  
  <p>EDIT</p>
  
  <pre><code>CREATE TABLE `data` (
  `date_time` decimal(26,6) NOT NULL,
  `channel_id` mediumint(8) unsigned NOT NULL,
  `value` varchar(40) DEFAULT NULL,
  `status` tinyint(3) unsigned DEFAULT NULL,
  `connected` tinyint(1) unsigned NOT NULL,
  `current` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`channel_id`,`date_time`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  
  
  CREATE TABLE `current` (
  `date_time` decimal(26,6) NOT NULL,
  `channel_id` mediumint(8) unsigned NOT NULL,
  `value` varchar(40) DEFAULT NULL,
  `status` tinyint(3) unsigned DEFAULT NULL,
  `connected` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`channel_id`)
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>These are the tables I am currently dealing with. I would like to either find a good way to union these for queries (in which case the current column in the data table is not needed) or just use the data table and for a given channel_id find the one row for that channel_id that has current = 1 and if it exists, update it with a new date_time, value, status, and connected. If one doesn't exist, add it with those same values.</p>