MySQL Forums
Forum List  »  InnoDB

Re: Can insert update a table based on match of one column value?
Posted by: Shawn Green
Date: February 07, 2019 04:02PM

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...

data = data_value
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

Options: ReplyQuote

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.