Database-specific providers usually access the underlying data store directly and offer the best performance, broadest functionality, and support for database-specific features.
Since a data provider needs only to implement a set of standard interfaces, the capabilities and performance of data providers for the same data source can differ significantly.
It is highly recommended that an application which seeks to employ natural primary keys with mutable values to use the In those cases when a database that does not support referential integrity is used, and natural primary keys with mutable values are in play, SQLAlchemy offers a feature in order to allow propagation of primary key values to already-referenced foreign keys to a limited extent, by emitting an UPDATE statement against foreign key columns that immediately reference a primary key column whose value has changed.
The primary platforms without referential integrity features are My SQL when the .
4) ask your DBA why he or she would ever use as a primary key data that might need to be changed. maybe you should not ask this question)Sorry I have not been watching this forum.
1) first create a copy of the orginal parent record with a new Primary key.
3) delete the old parent record because you dont need it any more.
So if there is a UNIQUE(a,b) constraint on the table in the example, then the INSERT is equivalent to this UPDATE statement: UPDATE table SET c=c 1 WHERE a=1 AND b=2;(and not "a=1 OR b=2") Regarding the trick for making LAST_INSERT_ID() well defined for updates: INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3; This does not work if nothing changes, presumably because My SQL doesn't run the dummy update at all then. if there is an existing record with 3 in column c then LAST_INSERT_ID() still won't return the AUTO_INCREMENT id afterwards.
I'm not sure whether this should be regarded as a bug or not, but it does make the construct less useful.
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.