CodeIgniter Forums
Insert or update if key exists - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Insert or update if key exists (/showthread.php?tid=37756)



Insert or update if key exists - El Forum - 01-19-2011

[eluser]Bramme[/eluser]
Hey everybody

I have a table that stores options selected by a user. I want every user to have a record in that table that gets upated as he makes changes. When a new user hasn't selected any options in the past, he ofcourse doesn't have a record.

The easy way out would be to DELETE FROM table WHERE account_id = $id and then just insert a new row for the user, but I don't like to do those two queries.

I read this page of the mysql documentation: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html and thought it would work for me, but apparently it doesn't.

I have put a "unique" key on my account_id field and use following query:

INSERT INTO table (option_a, option_b, account_id) VALUES ('val1', 'val2', $id) ON DUPLICATE KEY UPDATE account_id = $id

but this doesn't seem to work: a record is inserted the first time it is executed, and no new queries are added after it is ran again, but it also doesn't update the first record.

Did I do anything wrong?


Insert or update if key exists - El Forum - 01-19-2011

[eluser]Bramme[/eluser]
And I've solved it myself (don't mind if I double-post it here, for future reference):

After reading the docs again, more carefully, I realised that the query has to be:

INSERT INTO table (option_a, option_b, account_id) VALUES (‘val1’, ‘val2’, $id) ON DUPLICATE KEY UPDATE option_a = 'val1', option_b = 'val2'


Insert or update if key exists - El Forum - 01-19-2011

[eluser]Unknown[/eluser]
If this is MySQL, use "REPLACE INTO", it will take care of all the key-already-exists logic for you.


Insert or update if key exists - El Forum - 02-13-2011

[eluser]netricate[/eluser]
[quote author="Mark Neill" date="1295492062"]If this is MySQL, use "REPLACE INTO", it will take care of all the key-already-exists logic for you.[/quote]

I believe REPLACE will delete and insert, which can have side effects depending on your table setup.