+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: SQL - Insert or update if exist (/showthread.php?tid=69709)
SQL - Insert or update if exist - glorsh66 - 01-09-2018
What is the best way to insert a new line (and if it exists just update already existing values? )
i know about this construction - but as fat as i am aware - it messes around with ID.. (if we use autoinceremnt)
Code:
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;I also know obvious decision - just check if the line already exist -
just - simple if statement -
but i am afraid of loosing atomicity of this operation. In this case possible such scenario -
Two users are inserting one line, and check query may return false (the line doesn't exist) but before inserting newline another script makes it faster and we got an error (because in this case we need to use UPDATE instead of INSERT operation..)
RE: SQL - Insert or update if exist - Kaosweaver - 01-10-2018
You do an insert with the data and if you've got an existing unique key (which isn't auto-incremented), if it exists, the insert will throw an error.
Catch the error and then do an update.
Or
Use auto-incremented primary key and all inserts will be on a new line.
Or
Use the $this->db->sql("") and use the on duplicate key SQL to make it happen.