Note that REPLACE deletes before inserting!! Very dangerous. If you cannot use UPDATE .... ON DULICATE KEY UPDATE ... then do the following: You will need o send two queries to the server: INSERT IGNORE INTO tbl ......; UPDATE tbl SET ......;
Explanation: - the first query will try to insert the record. If the record already exists, it will abort without any error because there is IGNORE to ignore if the record already exists. So, if the record exists already, nothing will be done. - the second UPDATE query will update the record. If the first INSERT inserted the record, no problem because MySQL won't do anything as there is no change (the docs say that before updating, the engine checks if there is a change). If the insert failed (due to a duplicate), still there is no problem with the UPDATE because the record exists. For me, this is the ideal approach. Maybe, it is even the one used by MySQL when the query contains ON DULICATE KEY .... The remaining problem is then to know if the DB2 engine supports the IGNORE option!!! I have never used DB2, just find out!!!! Thanks Emery ----- Original Message ----- From: "Kelley Lingerfelt" <[EMAIL PROTECTED]> To: "Randy Chrismon" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, October 22, 2003 04:55 Subject: Re: Update Syntax > Can you use REPLACE? > > Kelley > > > > Randy Chrismon wrote: > > > Please tell me it ain't so... > > > > I am writing a Lotus Nots agent to feed data directly into a MySQL > > table. The agent needs to either insert a new record or update an > > existing record depending on whether a document in Nots is new or > > updated. Because this app may be ported over to a DB2 server, I am > > avoiding the "on duplicate key update" option. So, I intended to test > > for the existence of a MySQL record and, depending on the result, > > prepend either "Update my_table" to a build SQL string, or prepend > > "Insert into table". And then I read the documentation... It looks > > like Update MUST use the set column_1=new_value1, > > column_2=new_value2, > > etc. format. I was hoping to do something like "update my_table > > values(newValue1, newValue2...)" making sure to have a value or > > holder > > for each column. In other words, I was expecting insert and update to > > look pretty much the same except for the prefix and a where clause on > > the end of the update. Am I right that I can't do this?? > > > > Thanks. > > Randy > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
