Updating database in c

Posted by / 10-Jul-2017 20:15

Here's how i keep my totals uptodate when the prepays change...

INSERT INTO v8totals (tid,tyy,tmm,finances)SELECT '3218', YEAR(ppdate), MONTH(ppdate), SUM(ppamount) FROM bbprepay WHERE fkuserid='3218' GROUP BY YEAR(ppdate), MONTH(ppdate)ON DUPLICATE KEY UPDATE finances=(SELECT SUM(ppamount) FROM bbprepay WHERE fkuserid='3218' AND tyy=YEAR(ppdate) AND tmm=MONTH(ppdate) GROUP BY YEAR(ppdate), MONTH(ppdate))It might not be the best way to do an "Insert otherwise Update" but its working for me. :) If you need to update/insert a field and atomically get the previous value, here's a way to do the trick: SET @previous_note := NULL; INSERT INTO rencontre_note_moi_last_votes (id, note) VALUES (1, 2) ON DUPLICATE KEY UPDATE note = IF((@previous_note := note) NULL IS NULL is always [email protected]_note is set according to the value of a field, and that value is obviously the previous value, not the one being currently computed.

That information, along with your comments, will be governed by DISQUS’ privacy policy.My ultimate approach is to not use the DUPLICATE KEY UPDATE for this purpose.Rather, I explicitly test for existence using a SELECT, and only perform the INSERT if the SELECT fails.It appears to be the most recently inserted autoincrement value. To work around this, use the workaround from 5.1 and earlier: id=LAST_INSERT_ID(id) in the updating query. This example inserts into the database “mydb” and collection “mycoll”.

updating database in c-73updating database in c-85updating database in c-44

Better than Jon Webb's example mentioned above. Probably obvious to more experienced users, but the number of rows affected can be 0 as well as the 2 or 1 values mentioned above, if the ON DUPLICATE KEY UPDATE does not change the existing column values.