Hi Heikki, everyone, Other than changing all my REPLACE INTO codes to UPDATE, does anyone have a quick solution to this?
I have quite alot of code that makes use of what I thought REPLACE INTO was supposed to be doing... so was hoping that someone has managed to come up with some neat trick that can "simulate" the "UPSERT". :) Thanking in advance. Regards, Tan Shao Yi ----- Original Message ----- From: Heikki Tuuri Date: February 16 2004 9:12am Subject: Re: REPLACE INTO and CASCADEs. Hi! The REPLACE + FOREIGN KEY behavior indeed changed between 4.0.17 -> 4.0.18, because the old behavior was wrong, according to the MySQL manual: " Fixed a bug: MySQL should not let REPLACE to perform internally an UPDATE if the table is referenced by a FOREIGN KEY. The MySQL manual states that REPLACE must resolve a duplicate key error semantically with DELETE(s) + INSERT, and not by an UPDATE. In versions < 4.0.18 and < 4.1.2, MySQL could resolve a duplicate key conflict in REPLACE by doing an UPDATE on the existing row, and FOREIGN KEY checks could behave in a semantically wrong way. (Bug #2418) " I am sorry for the inconvenience. The old behavior was maybe more intuitive, if you think of REPLACE as an 'UPSERT' command. But in MySQL it is a 'DELSERT' command. Best regards, Heikki On Sun, 15 Feb 2004, Tan Shao Yi wrote: > Hello, > > I just upgraded from 4.0.17 to 4.0.18 on a Linux box and realised the > behaviour of REPLACE INTO appears to have changed. > > I have two tables: table2 references table1 with ON DELETE CASCADE and ON > UPDATE CASCADE on a primary key. > > Previously in 4.0.17 when I issued a REPLACE INTO on table1, only the > row in table1 gets "replaced". No rows in table2 get deleted. > > Now, in 4.0.18, when I issue a REPLACE INTO on table1, the row in table1 > gets "replaced", but this replacement gets cascaded into table2 and all > rows in table2 with the primary key get deleted. > > Has anyone encountered something similar? I have only one box available so > I am not able to re-test my observations again (I have reverted to > 4.0.17). > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
