MySQL Forums
Forum List  »  InnoDB

Re: Can insert update a table based on match of one column value?
Posted by: nobody nobody
Date: February 07, 2019 07:15PM

Shawn Green Wrote:
-------------------------------------------------------
> 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.

Yes, I was using the insert on duplicate as an example, it does not have condition check, if it was, my problem is resolved :-).


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

Are there any check row function found_rows() or if @@rowcount = 0 available in C++ after update? if it does, I'll use:

update data_table set data = data_value WHERE where id = id_value and timestamp < timestamp_now - 86400 if @@rowcount = 0 insert ....

As Peter said that stored procedure does not have good performance than the normal SQL queries, so I am still trying if I can get sql queries work.

Thanks very much Shawn.

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.