Re: Can insert update a table based on match of one column value?

[Shawn Green]

In this thread you have made two logic mistakes.

First, you cannot tell the INSERT ... ON DUPLICATE KEY UPDATE... command which condition it needs to use to decide between the INSERT or UPDATE action. That test is hardcoded as a check against the PRIMARY KEY of the table.

Second, you could use your EXISTS() test but you need to move it into the WHERE clause of your UPDATE command, without the IF or EXISTS, like this...


update
data_table
set
data = data_value
WHERE
where id = id_value
and timestamp < timestamp_now - 86400

You would then need to check (from your client, if you are outside of a stored program) how many rows were changed to see if you need to follow that up with an INSERT command. The transactional sequence pattern Peter outlined earlier would be better if that were the case.

And Peter was right on his other point (as always), conditional flow control constructs (like "if..then..else..end if") are only valid within the scope of a stored program (stored procedure, stored function, event, or trigger). They are not part of the "immediate execution" syntax.

SQL is not a natively iterative language. It is a command system designed for set-wise operations against rows of data (tuples). The other, more iterative, parts of the language arrived later to help automate those set-wise operations.

--
Shawn Green
MySQL Senior Principal Technical Support Engineer