CODEWITHSUNDEEP
phpmysqlsql
zkanoca
zkanoca

Reputation: 9918

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

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.

Upvotes: 1

Views: 674

Answers (2)

Madcoe
Madcoe

Reputation: 213

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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;

Upvotes: 4

Related Questions