Home sql How to update an existing record with ON Duplicate Key Update?

How to update an existing record with ON Duplicate Key Update?

Author

Date

Category

Hello!

Today I first encountered On Duplicate Key Update I did not fully understand how it works.

Making a request

db () - & gt; query ("Insert Into". BB_BT_USER_STATS. "
  (Topic_ID, User_ID, Date_Comleted)
  SELECT
  topic_id, user_id, ". TimeNow."
  From tmp_users_stats.
  ON Duplicate Key Update
  Date_Comleted = What is there to write ");

As a result I want to get an update in the table. If Topic_id and User_ID already exist (unique), and date_comleted is different, a new line with new Data.


Answer 1, Authority 100%

For example, in MySQL, in order to extract the value from Insert data, you should use the Values ​​() function

db () - & gt; query ("Insert Into". BB_BT_USER_STATS. "
  (Topic_ID, User_ID, Date_Comleted)
  SELECT
  topic_id, user_id, ". TimeNow."
  From tmp_users_stats.
  ON Duplicate Key Update
  Date_Comleted = Values ​​(Date_Comleted) ");

Answer 2, Authority 33%

If the topic_id + user_id is unique, you can use the REPLACE TO instead of insert to, in this case the recording will be restarted in the event of an existence or will be created, if there were no such keys before. Only it is necessary to consider that in the case of Replace, the old entry will be removed, and then the new one with the same keys will be inserted. If there are constraints with ON DELETE CASCADE, then the data may die

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions