Fork me on GitHub
Subscribe 2

Ticket #1031 (open enhancement)

Replace UPDATE and INSERT queries by REPLACE INTO

  • Created: 2015-04-11 11:35:14
  • Reported by: chris98
  • Assigned to: None
  • Milestone: None
  • Component: code
  • Priority: normal

In admin_forums.php there is this code:

// Run an UPDATE and see if it affected a row, if not, INSERT

$db->query('UPDATE '.$db->prefix.'forum_perms SET read_forum='.$read_forum_new.', post_replies='.$post_replies_new.', post_topics='.$post_topics_new.' WHERE group_id='.$cur_group['g_id'].' AND forum_id='.$forum_id) or error('Unable to insert group forum permissions', __FILE__, __LINE__, $db->error());

if (!$db->affected_rows())
	$db->query('INSERT INTO '.$db->prefix.'forum_perms (group_id, forum_id, read_forum, post_replies, post_topics) VALUES('.$cur_group['g_id'].', '.$forum_id.', '.$read_forum_new.', '.$post_replies_new.', '.$post_topics_new.')') or error('Unable to insert group forum permissions', __FILE__, __LINE__, $db->error());

Wouldn't it make more sense to just use REPLACE INTO?

// Run an UPDATE and see if it affected a row, if not, INSERT

$db->query('REPLACE INTO '.$db->prefix.'forum_perms (group_id, forum_id, read_forum, post_replies, post_topics) VALUES('.$cur_group['g_id'].', '.$forum_id.', '.$read_forum_new.', '.$post_replies_new.', '.$post_topics_new.')') or error('Unable to insert group forum permissions', __FILE__, __LINE__, $db->error());

History

chris98 2015-04-11 11:36:43

  • Description changed. (Diff)

adaur 2015-04-11 11:57:15

According to http://www.quora.com/In-MySQL-when-shou … KEY-UPDATE

If you utilize REPLACE INTO and you have an AUTO_INCREMENT field (i.e. index/id column), that auto increment column will be incremented.

We don't want that.

http://stackoverflow.com/questions/2110 … -vs-update

Replace was designed to ease the following case:

    Check, if record with same PK exists
    If yes, update the record with the given one
    If no, insert a new record

As per documentation REPLACE is equivalent to INSERT, apart from deleting any existing record having the PK is beeing deleted before.

REPLACE INTO will delete the existing row and insert a new one. I'm not sure that we want this behaviour.