From the documentation: set() will also accept an optional third parameter ($escape), that will prevent data from being escaped if set to FALSE. So this should work to pass the increment statement directly to the database: $this->db->set(‘usage’, ‘usage+1’, FALSE); $this->db->where(‘tag’, ‘java’); $this->db->update(‘tags’); Because it’s not escaped, if you’re using a variable instead of a fixed … Read more
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″;
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
With Grijesh’s perfect help and his suggestion to use conditional statements, I was able to get ONE trigger that does both tasks. Thanks again Grijesh DELIMITER $$ CREATE TRIGGER update_count AFTER INSERT ON ext_words FOR EACH ROW BEGIN IF NOT EXISTS (SELECT 1 FROM ext_words_count WHERE word = NEW.word) THEN INSERT INTO ext_words_count (word) VALUES … Read more
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
Semantically you are asking “insert Competitors where doesn’t already exist”: INSERT Competitors (cName) SELECT DISTINCT Name FROM CompResults cr WHERE NOT EXISTS (SELECT * FROM Competitors c WHERE cr.Name = c.cName)
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
9.5 and newer: PostgreSQL 9.5 and newer support INSERT … ON CONFLICT (key) DO UPDATE (and ON CONFLICT (key) DO NOTHING), i.e. upsert. Comparison with ON DUPLICATE KEY UPDATE. Quick explanation. For usage see the manual – specifically the conflict_action clause in the syntax diagram, and the explanatory text. Unlike the solutions for 9.4 and … Read more
Use INSERT … ON DUPLICATE KEY UPDATE QUERY: INSERT INTO table (id, name, age) VALUES(1, “A”, 19) ON DUPLICATE KEY UPDATE name=”A”, age=19