MySQL update if exists else insert

Question

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).

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?

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?

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.

Is there a better solution? The table will have millions of entries and there will be hundreds of thousands of channel_ids.

EDIT

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;

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.

sreimer · Accepted Answer · 2011-04-05 19:56:39Z

3

Look at MySQL's ON DUPLICATE KEY UPDATE syntax

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

answered Apr 5, 2011 at 19:56

sreimer

5,0082 gold badges36 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patrick Over a year ago

I don't think I can use that, because I can't see a way to create a unique id on channel_id and current.

Joseph Lust Over a year ago

And don't forget to reference the passed values, in the UPDATE statement, using the VALUES(col_name) accessor. I had to dig for an example of that the first time I needed it.

Kenaniah · Accepted Answer · 2011-04-05 19:55:59Z

1

http://dev.mysql.com/doc/refman/5.0/en/replace.html

answered Apr 5, 2011 at 19:55

Kenaniah

5,20126 silver badges28 bronze badges

1 Comment

Patrick Over a year ago

I guess I swapped REPLACE and UPDATE in my question. I edited it to fix that. I don't think I can use REPLACE, because I can't see a way to create a unique index on channel_id and current, unless I create an entire new column of unique values.

Kevin Peno · Accepted Answer · 2011-04-05 20:17:27Z

0

You absolutely can create a compound UNIQUE key.

CREATE UNIQUE INDEX ON table_name ( `channel_id`, `current` );

Then, per the other answers, you can use either REPLACE or ON DUPLICATE to update. Obviously this is a terrible idea and reformation of the table should probably happen, but it will work.

answered Apr 5, 2011 at 20:17

Kevin Peno

9,2261 gold badge36 silver badges56 bronze badges

9 Comments

Patrick Over a year ago

But if there are many rows with the same channel_id and current = 0? How could I change the table?

Kevin Peno Over a year ago

You will likely have to not use something like current and instead use something real like archived_date. This means you will have to update the date, but there's little use you can do at this point.

Patrick Over a year ago

All the values have a Unix time associated with them, including the current value. But sometimes the current value will become the archived value, and I wouldn't want to overwrite that.

Kevin Peno Over a year ago

Sounds to me like you should use channel_id and unix_time as the key then. You don't need ot update the time to use it, so long as it is always different.

Patrick Over a year ago

But the new current value will have a new time, and the key has to be the same to update that row. And if the old current value becomes an archived value, I would overwrite it using just its channel_id and time.

|

Collectives™ on Stack Overflow

MySQL update if exists else insert

3 Answers 3

2 Comments

1 Comment

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related