Hello!

Today for the first time I ran into ON DUPLICATE KEY UPDATE did not fully understand how it works.

I make a request

 DB()->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 = что тут писать");

As a result, I want to receive an update in the table if topic_id and user_id already exist (unique), and date_comleted is different, otherwise a new line is created with new data.

  • since there is an equal sign - a constant, most likely a date. And what - such that records would differ, such as you need. - nick_n_a 3:51 pm
  • If in the case of a duplicate you need to insert a string and not update the field, then maybe you need on duplicate key insert ? Maybe this topic will help you on stackoverflow.com/questions/3884344/… - nick_n_a

2 answers 2

For example, in MySQL, in order to extract a value from an INSERT data, use the function VALUES()

 DB()->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)");

    If the topic_id + user_id is unique, then you can use REPLACE TO instead of INSERT TO, in which case the entry will be overwritten if existing, or a new one will be created if there are no such keys before. Only it is necessary to take into account that in the case of REPLACE, the old record will be deleted, and then a new one will be inserted with the same keys. If there are constraints with ON DELETE CASCADE actions, then the data may die.