As an example, imagine I want to log the outside temperature in a table. It is read every minute but changes only 20 times a day or so, hence I don't want useless entries every minute. A value shall only be written when it is != the last saved value.
Currently I am using two separate queries. One SELECT to get the last value, then one INSERT if necessary.
Now I am curious if this can be done in a single query somehow like
@lastval = (SELECT ... ORDER BY datetime DESC LIMIT 1); IF (@lastval != newVal) THEN INSERT ...
But it seems that IF is not possible for this. Indeed there are lots of threads about "how to avoid duplicates", sometimes advised to solve with UNIQUE indexes and whatever, but all this doesn't apply to what I need.
I also saw some monstrous queries to solve problems that perhaps are like mine, but before doing that I'd prefer to stay with my solution.
Does an elegant modification exist?
You can add a WHERE even when not selecting from a table, so:
INSERT INTO temp
SELECT NOW(), @newVal
WHERE NOT @newVal <=> (SELECT val FROM temp ORDER BY datetime DESC LIMIT 1)
;
Decomposing it:
Your intuition on ORDER BY datetime DESC LIMIT 1 was right, you can use it to select a pseudo row:
SELECT NOW(), @newVal
WHERE @newVal <> (SELECT val FROM temp ORDER BY datetime DESC LIMIT 1);
(SELECT WHERE without a FROM can be seen as a conditional VALUES)
Then we have to handle the case where your table is empty:
COALESCEing the comparison to TRUE before it is evaluated by the WHERE: COALESCE(@newVal <> (SELECT …), TRUE)NULL-safe equal, negated: NOT @newVal <=> (SELECT …)Finally we use this SELECT in an INSERT … SELECT structure.
Here in a small example with 5 values input, but only 3 retained.