Updating database in c
Rob Smeets While it probably goes without saying, you need to include the auto increment column or you can cause an insertion anomaly with an overriding signature in the INSERT statement.
I'd suggest something here with a code example. id=45081 This won't work: INSERT INTO xxx(id,val) SELECT a,b FROM yyyy ON DUPLICATE KEY UPDATE val = VALUES(val)Taking value that will be inserted with VALUES() only works with INSERT INTO .... Here is a nice tip for INSERT INTO SELECT ON DUPLICATE KEY UPDATE.
That way, a new "note" is inserted of the "note" is changed, and the previous value is returned. The 'work around' suggested in the documentation to obtain the ID of a row updated using the DUPLICATE KEY UPDATE clause of an INSERT statement has a problem in addition to those mentioned by earlier posts.
Namely, if you are using INNODB storage engine and have a FOREIGN KEY referencing the primary key of the table being updated, this strategy may fail with: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ....
When replacing a document, the replacement document must consist of only field/value pairs; i.e. The replacement document can have different fields from the original document.
In the replacement document, you can omit the field, pass an entirely new document as the second argument to replace One() When replacing a document, the replacement document must consist of only field/value pairs; i.e. The replacement document can have different fields from the original document.
A way to make things work is to use a dummy column, so if you have a table with auto_increment column ID and unique key a,b and a smallint dummy column for instance, the query might look like this: INSERT INTO test (a,b) VALUES ('1','2') ON DUPLICATE KEY UPDATE ID=LAST_INSERT_ID(ID), Dummy = NOT dummy; Now, SELECT LAST_INSERT_ID(); will return the correct ID.
Example: Update-Select-Group By I have a table of totals 'v8totals' with Primary Key=(tid,tyy,tmm) and a table of records 'bbprepay'.