Insert only if values differ from the previous record with multi insert?

Question

I can't reveal the actual kind of data I'm storing but imagine I have a database of weatherstations and their updates.

The updates-table would be something like the following:

CREATE TABLE `updates` (
    `weatherStationId` INT NOT NULL,
    `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `temperature` DECIMAL(10,7) NULL,
    `humidity` DECIMAL(10,7) NULL,
    CONSTRAINT `FK__weatherstations` FOREIGN KEY (`weatherStationId`) REFERENCES `weatherstations` (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB

And look something like this for instance:

╔══════════════════╦═════════════════════╦═════════════╦══════════╗
║ weatherStationId ║        time         ║ temperature ║ humidity ║
╠══════════════════╬═════════════════════╬═════════════╬══════════╣
║               69 ║ 2015-03-06 00:04:56 ║           4 ║       29 ║
║             1337 ║ 2015-03-08 12:22:33 ║          29 ║       38 ║
╚══════════════════╩═════════════════════╩═════════════╩══════════╝

When there's an update, I'd like to compare the new values(temperature & humidity) of those of the previous ones, e.g. the record with the latest time-value among those of the same weatherstationId as the one we've got an update for. If those values are not the same, or there are no stored updates for that weatherstation then I simply want to insert the record with the new values. Otherwise I'd like to change the time of the latest record to now().

So with an update for weatherId 69 with a temperature of 4 and humidity of 30 at 2015-03-07 12:00:00 the table above would become:

╔══════════════════╦═════════════════════╦═════════════╦══════════╗
║ weatherStationId ║        time         ║ temperature ║ humidity ║
╠══════════════════╬═════════════════════╬═════════════╬══════════╣
║               69 ║ 2015-03-06 00:04:56 ║           4 ║       29 ║
║             1337 ║ 2015-03-08 12:22:33 ║          29 ║       38 ║
║               69 ║ 2015-03-09 12:00:00 ║           4 ║       30 ║
╚══════════════════╩═════════════════════╩═════════════╩══════════╝

(Values differ from rest so insert new record)

With the same update except with a humidity of 29 instead, the table should now look like this:

╔══════════════════╦═════════════════════╦═════════════╦══════════╗
║ weatherStationId ║        time         ║ temperature ║ humidity ║
╠══════════════════╬═════════════════════╬═════════════╬══════════╣
║               69 ║ 2015-03-09 12:00:00 ║           4 ║       29 ║
║             1337 ║ 2015-03-08 12:22:33 ║          29 ║       38 ║
╚══════════════════╩═════════════════════╩═════════════╩══════════╝

(New values are same as previous, so only update the time in the old record to that of the new record. or if you will, insert new record and delete the old)

Currently I do this in PHP which is a no-brainer. As of now I have no real issues with this approach. I just thought there might be a way of doing it all with one sql-statement which I suspect would mean less and more clean code, as well as better performance as I think that could become a small issue as the database grows.

I found the following question&answer that deals with a similar issue MySQL: Do not insert a new row if defined rows are same

The problem is that in my case I'm inserting updates for tens of thousands of "weatherstations" at once. And I was unable to get that solution to work with a multi-insert. Is there a way?

http://stackoverflow.com/questions/28419094/update-row-on-update-trigger-multi-table-condition — Jean-François Savard, Mar 06 '15 at 12:26
-1 until you have not shown anything clear to us with your effort. Without any sample your desired output is not clear. Imagine is a wrong word here. make a sample table, put 2,3 rows, show your multi insert query then tell what your table should look like after that insertion. It will be plus one and get answer as well. I can answer but i am not sure you could be thinking same as me. Better to come with an sqlfiddle link. See this question as sample http://stackoverflow.com/questions/12653678/group-concat-in-concat-not-working-with-null-values — Sami, Mar 06 '15 at 13:12
@Sami I updated the question. Added some samples and also updated my desired result. I do not want to do nothing when values match anymore, but rather update time of old record. — Clox, Mar 07 '15 at 11:53

score 0 · Answer 1 · answered Mar 06 '15 at 22:45

Setup a BEFORE UPDATE TRIGGER on a new table containing the Current readings. Change the INSERTs to instead UPDATE Current. The trigger would be coded to discover whether anything changed, and, only if it did, it would proceed to INSERT into the table you described.

score 0 · Answer 2 · answered Mar 06 '15 at 22:49

To get the multi-row INSERT to work, you need to collect the data in some way, then batch INSERT them.

Plan A: Collect them in your non-mysql code somehow. If this is possible it is the better approach.

Plan B: Have a "staging table" that is perhaps MyISAM without any indexes. INSERT your readings into it. Then, between reading times, do INSERT INTO Real SELECT * FROM Staging and TRUNCATE TABLE staging.

Insert only if values differ from the previous record with multi insert?

2 Answers2

Linked