My friend Joe Stump ran into a problem today with MySQL’s REPLACE INTO functionality. REPLACE INTO does a full-on DELETE of the record, then a new INSERT. When you have triggers that do things based on DELETEing, the REPLACE INTO trips the DELETE triggers, which caused unintended consequences.
I first got excited when I heard about “ON DUPLICATE KEY UPDATE” functionality in MySQL, but realized that it’s just a way of letting you type in a full UPDATE command. I realize there might be situations where you’d want a different UPDATE statement than the values in an INSERT statement, but it would be really useful if you could write something like
INSERT INTO foo (id,name,email) values (4,’mike’,'mgkimsal@gmail.com’) ON DUPLICATE KEY UPDATE;
and have MySQL replicate mSQL’s old ‘INSERT OR UPDATE’ functionality.
