How to INSERT a record or UPDATE if it already exists?
MySQL supports the insert-on-duplicate syntax, f.e.: INSERT INTO table (key,col1) VALUES (1,2) ON DUPLICATE KEY UPDATE col1 = 2;
insert-update
How to update elements within a heap? (priority queue)
Typical Solution The usual solution is to mark an element as invalid and insert a new element, then eliminate the invalid entries as they are popped-off. Alternative Solution If that approach doesn’t suffice, it is possible restore the min-heap invariant in O(log n) steps as long as the location of the value being changed is … Read more
T-SQL Insert or update
Both work fine, but I usually use option 2 (pre-mssql 2008) since it reads a bit more clearly. I wouldn’t stress about the performance here either…If it becomes an issue, you can use NOLOCK in the exists clause. Though before you start using NOLOCK everywhere, make sure you’ve covered all your bases (indexes and big … Read more
Does DB2 have an “insert or update” statement?
Yes, DB2 has the MERGE statement, which will do an UPSERT (update or insert). MERGE INTO target_table USING source_table ON match-condition {WHEN [NOT] MATCHED THEN [UPDATE SET …|DELETE|INSERT VALUES ….|SIGNAL …]} [ELSE IGNORE] See: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0010873.htm https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0010873.html https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/merge?lang=en
Is there a way to do an “INSERT…ON DUPLICATE KEY UPDATE” in Zend Framework 1.5?
I worked for Zend and specifically worked on Zend_Db quite a bit. No, there is no API support for the ON DUPLICATE KEY UPDATE syntax. For this case, you must simply use query() and form the complete SQL statement yourself. I do not recommend interpolating values into the SQL as harvejs shows. Use query parameters. … Read more
.NET Dictionary: get existing value or create and add new value
We have a slightly different take on this, but the effect is similar: public static TValue GetOrCreate<TKey, TValue>(this IDictionary<TKey, TValue> dict, TKey key) where TValue : new() { if (!dict.TryGetValue(key, out TValue val)) { val = new TValue(); dict.Add(key, val); } return val; } Called: var dictionary = new Dictionary<string, List<int>>(); List<int> numbers = dictionary.GetOrCreate(“key”); … Read more
Update a column in MySQL
You have to use UPDATE instead of INSERT: UPDATE Syntax For Example: UPDATE table1 SET col_a=”k1″, col_b=’foo’ WHERE key_col=”1″; UPDATE table1 SET col_a=”k2″, col_b=’bar’ WHERE key_col=”2″;
Why are 2 rows affected in my `INSERT … ON DUPLICATE KEY UPDATE`?
From the manual: With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated.
INSERT INTO … SELECT FROM … ON DUPLICATE KEY UPDATE
MySQL will assume the part before the equals references the columns named in the INSERT INTO clause, and the second part references the SELECT columns. INSERT INTO lee(exp_id, created_by, location, animal, starttime, endtime, entct, inact, inadur, inadist, smlct, smldur, smldist, larct, lardur, lardist, emptyct, emptydur) SELECT id, uid, t.location, t.animal, t.starttime, t.endtime, t.entct, t.inact, t.inadur, … Read more
How do I update if exists, insert if not (AKA “upsert” or “merge”) in MySQL?
Use INSERT … ON DUPLICATE KEY UPDATE. For example: INSERT INTO `usage` (`thing_id`, `times_used`, `first_time_used`) VALUES (4815162342, 1, NOW()) ON DUPLICATE KEY UPDATE `times_used` = `times_used` + 1