MySQL: Do not insert a new row if defined rows are same

Question

I have a table which I get sampling values: AeroSamples

id    time    temperature    pressure    humidity

I sample the values at a 5 minute period. Before inserting a new row into the table, I check if the last row's temperature, pressure and humidity values are same with current values. If so, I do not want to add a new row. Else A new record could be added.

I do this like that:

SELECT temperature, pressure, humidity FROM AeroSamples ORDER BY id DESC LIMIT 1

When I get the last values, I compare three fields with current values which is the way I do not like:

if($row["temperature"] !== $curTemp || $row["pressure"] !== $curPres || $row["humidity"] !== $curHumi)
{
    $db->prepare("INSERT INTO AeroSamples (temperature, pressure, humidity) VALUES(:t,:p,:h)");
    ...
}

How can I do this SQL only?

Does ON DUPLICATE KEY UPDATE ... help me? I do not think so. Because I am not sure if it is valid for multiple fields at a time.

@DigitalChris If at least one field is different it should be recorded. If the 3 fields' all are same will not be recorded. Changing ORs to ANDs will not help me if only one field is different. — zkanoca
– zkanoca, Commented Dec 27, 2013 at 15:29
Also, this question gives me the willies. Unchanged data is entirely different from missing data. In analyzing the data you want to differentiate between 2 hours of unchanged weather and 2 hours of server downtime. — Digital Chris
– Digital Chris, Commented Dec 27, 2013 at 15:29
@DigitalChris I do not care about server downtime matters. Just want to know if this check could be done using MySQL only. — zkanoca
– zkanoca, Commented Dec 27, 2013 at 15:32

Gordon Linoff · Accepted Answer · 2013-12-27 15:34:59Z

4

The previous values will not be the same, because the time is different. Alas.

You can do this using the insert . . . select syntax. The idea is to select the last row inserted and use a where clause to filter the rows. The filter will return no rows (and hence no insert) when the values are the same:

insert into AeroSamples(temperature, pressure, humidity) 
    select :t, :p, :h
    from (select temperature, pressure, humidity
          from AeroSamples
          order by id desc
          limit 1
         ) as1
    where as1.temperature <> :t or as1.pressure <> :p or as1.humidity <> :h;

answered Dec 27, 2013 at 15:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Clox Over a year ago

Nice answer. I'd like to do this too but in my case I do a multirow insert with thousands of rows. Would there be any way of making it do this check for each row?

Gordon Linoff Over a year ago

@clox . . . You should ask another question with details about what you want to happen. But, yes, I think this can be adapted.

Clox Over a year ago

Yeah, I did that too =) stackoverflow.com/questions/28898733/…

Madcoe · Accepted Answer · 2013-12-27 15:37:47Z

0

In order to use ON DUPLICATE you will need to add a unique index to your table.

create unique index aerosamples_ux1 on AeroSamples(temperature, pressure, humidity);

than you can use ON DUPLICATE KEY UPDATE or ON DUPLICATE KEY IGNORE inside your queries... also keep in mind if you dont use ON DUPLICATE you query will give you an error and won't add a duplicate record after adding this index.

answered Dec 27, 2013 at 15:37

Madcoe

2131 silver badge6 bronze badges

2 Comments

lafor Over a year ago

There should be no duplicating combinations of temparature, pressure and humidity in two neighbouring rows, not the entire table.

Madcoe Over a year ago

Yo are right in this case it may not be useful unless you add another field to database and index. I just wanted to clarify use case of ON DUPLICATE because it was a part in original question.

Collectives™ on Stack Overflow

MySQL: Do not insert a new row if defined rows are same

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related