Insert value into mysql if this value isn't the latest value

Question

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?

Is it possible to use a stored procedure? Is it possible to add the check in the programming language/program which access/uses the database? — Progman
– Progman, Commented Aug 16 at 15:13
@Progman stored procedure - would be possible, but seems a bit overhead to me. add the check in program - that's what I currently do. The program executes two statements, one for the check and one for the INSERT. My question is not vital for the code, it came up out of curiosity and the eagerness to make things better if possible :-) — Droidum
– Droidum, Commented Aug 17 at 18:53

Michał Turczyn · Accepted Answer · 2025-08-16 18:42:18Z

4

You can use this format:

SET @newTemp = 23.5;

INSERT INTO readings (ts, temp)
SELECT NOW(), @newTemp
FROM DUAL
WHERE (SELECT temp FROM readings ORDER BY ts DESC LIMIT 1) <> @newTemp
   OR (SELECT COUNT(*) FROM readings) = 0;

edited Aug 16 at 18:42

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

answered Aug 16 at 15:39

Fahad Hasan

412 bronze badges

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

3 Comments

Bill Karwin Aug 16 at 15:45

Upvoted, but you might like to know that you can skip FROM DUAL because in MySQL 8.0 and later, you can use SELECT with WHERE even without a FROM clause. Example: SELECT 123 WHERE false; returns zero rows.

Droidum Aug 18 at 9:23

Also upvoted, but I like the other solution a bit more because it appears shorter.

Akina Aug 18 at 9:42

@Droidum Brevity in itself doesn't mean anything, it's good when it's effective. The solution provided by Guillaume Outters is more effective because it uses only one subquery.

Guillaume Outters · Accepted Answer · 2025-08-18 08:50:55Z

2

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:

either COALESCEing the comparison to TRUE before it is evaluated by the WHERE: COALESCE(@newVal <> (SELECT …), TRUE)
or better and simpler, as suggested by @Akina, use the 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.

edited Aug 18 at 8:50

answered Aug 16 at 15:45

Guillaume Outters

7,5041 gold badge22 silver badges28 bronze badges

2 Comments

Akina Aug 18 at 5:06

I'd recommend to use .. WHERE NOT @newVal <=> (SELECT .. instead of COALESCE. dbfiddle.uk/pU-i1h3n Logical operator is more fast then function call.

Guillaume Outters Aug 18 at 8:51

Thanks @Akina for the hint! Not using MySQL, I didn't know this operator (only its extremely verbose SQL standard equivalent IS NOT DISTINCT FROM on other databases). It's always good to learn.

Guillaume Outters · Accepted Answer · 2025-08-17 06:23:01Z

-1

INSERT INTO readings (ts, temp)
SELECT NOW(), @newTemp
    WHERE NOT EXISTS (SELECT 1 FROM readings)
       OR (SELECT temp FROM readings ORDER BY ts DESC LIMIT 1) <> @newTemp;

edited Aug 17 at 6:23

Guillaume Outters

7,5041 gold badge22 silver badges28 bronze badges

answered Aug 17 at 1:28

Rick James

144k15 gold badges144 silver badges254 bronze badges

1 Comment

Guillaume Outters Aug 17 at 6:28

Your initial WHERE NOT EXISTS (SELECT 1 FROM readings /* nothing here */ ORDER BY ts DESC LIMIT 1) <> @newTemp looked truncated (resulting in bizarre NOT EXISTS(… ORDER BY …) and NOT EXISTS() <> @newTemp), I edited it to what I think was your intent: feel free to reedit if necessary. But wasn't you wish to simply comment that Fahad Hasan's COUNT(*) or my COALESCE() could be replaced by a NOT EXISTS ()?

Collectives™ on Stack Overflow

Insert value into mysql if this value isn't the latest value

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related