This will also solve Nikolay Pelov's problem in the previous post.insertinto _Rank_Author(id Author, publication Count, citation Count, co Author Count)select Author, @publication Count := count(distinct Paper), 0, 0from author_paper apgroup by Authoron duplicate key update publication Count = @publication Count; I don't see it documented, but it looks like NULL values do not trip the "duplicate key" feature. Old values being referenced in UPDATE are getting replaced one by one from left to right to their new values.Consider a unique key definition like this: UNIQUE KEY `friend_id` (`friend_id`,`type`,`status`)Putting in two rows with identical values in those three fields should obviously not be allowed, but it can happen if the field value happens to be NULL in both rows. This is counter-intuitive given that VALUES are being applied simultaneously and UPDATE expressions are not.
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.The trick is to use user-defined variable to store computed data, so that it is not need to be computed again. Don't forget that UPDATE in My SQL is not standard-conformant.Doing SELECT and then INSERT is not a complete replacement of INSERT ON DUPLICATE KEY UPDATE: there is a race condition involved.You will still need to check whether your INSERT works, and if it doesn't then you need to do an update.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.
In some databases, such as Postgre SQL, when a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the fromlist, and each output row of the join represents an update operation for the target table.
When using FROM, one should ensure that the join produces at most one output row for each row to be modified.
In other words, a target row shouldn't join to more than one row from the other table(s).
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.
Instead, you specify which conditions a row must meet in order to be updated.