CODEWITHSUNDEEP
mysqlmariadb
Saku Pieper
Saku Pieper

Reputation: 33

Conditional INSERT in MariaDB (MySQL)

I have two tables. The first contains the data about sensors (group, name, latest update, etc.). The second the readings for all sensors.

I now want to check if the latest update of a sensor is not too long ago, and if so, insert a null value into the readings table for that sensor.

I tried some IF statements, but without luck. Should I go for INSERT and "where IN()"?

Upvotes: 0

Views: 2006

Answers (1)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

You are in the right path. It's effectively an INSERT with a WHERE clause So what you need to do is create a record in the Readings table for Sensors that have an UPDATE past X days (10 in my example below)

Something like : 

insert into readings
select 1 as id, id as sens_id, null as info
from sensors
where datediff(now(),latest_update) > 10;

In my example, I provide an ID but I assume that it's an autoincrement integer in your BD...

and then, update the UPDATE field...

update sensors set latest_update = now()
where datediff(now(),latest_update) > 10;

Go, try it by yourself...

https://www.db-fiddle.com/f/u1EWRpH7nbxHEjwBDUgwLh/0

UPDATE:

https://www.db-fiddle.com/f/u1EWRpH7nbxHEjwBDUgwLh/1

If latest_update is indexed, I suggest this instead :

where date_add(now(),INTERVAL -10 DAY) > latest_update;

to benefit from the indexing.

"When you wrap a function around an indexed column SQL Server must compute the value of the function for each row in the table. When you just compare the indexed column to a scalar value or the result of a function then SQL Server can use that value to seek into the index."

Not sure exactly for MySQL since I didn't test it in this case but to be sure :)

https://www.sqlteam.com/articles/avoid-enclosing-indexed-columns-in-a-function-in-the-where-clause

Upvotes: 1

Related Questions