Conditional INSERT in MariaDB (MySQL)

Question

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()"?

Woud you please edit your question to provide sample data and expected output ? As it is, your question does not contain enough information to get an accurate answer. — GMB
– GMB, Commented Jan 20, 2019 at 0:01
i would take a look into a View WITH CHECK OPTION ... "The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts to rows for which the WHERE clause in the select_statement is not true"... In MySQL it can also be used to simulate the missing CHECK constraint for the CREATE TABLE statement. Make sure to use ALGORITHM=MERGE in the CREATE VIEW statement to get the best performance — Raymond Nijland
– Raymond Nijland, Commented Jan 20, 2019 at 16:50

Fabien TheSolution · Accepted Answer · 2019-01-20 04:53:24Z

1

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

edited Jan 20, 2019 at 4:53

answered Jan 20, 2019 at 4:27

Fabien TheSolution

5,0501 gold badge20 silver badges31 bronze badges

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

1 Comment

Raymond Nijland Over a year ago

MySQL/MariaDB has a better native way with a updateable view and with check option .. "The WITH CHECK OPTION clause is used to prevent updates or inserts to views unless the WHERE clause in the SELECT statement is true."... In MySQL it can also be used to simulate the missing CHECK constraint for the CREATE TABLE statement.. Make sure to use ALGORITHM=MERGE in the CREATE VIEW statement to get the best performance

Collectives™ on Stack Overflow

Conditional INSERT in MariaDB (MySQL)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related