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