Hi Douglas, Thanks for your prompt reply. I read through the description of REPLACE and tested it as well. It seems just fine, but the idea that it would DELETE and then insert if a match is found, seems like a warning to me.
I need to know this: can it happen that in between this delete and insert, another thread inserts a matching record? Do we have any alternative? Thanks, Ravi. -----Original Message----- From: Douglas Sims [mailto:[EMAIL PROTECTED] Sent: Tuesday, 29 August, 2006 10:48 AM To: Ravi Kumar. Cc: mysql@lists.mysql.com Subject: Re: Conditional Insert Hi Ravi Standard SQL syntax doesn't provide for that. You could wrap the whole thing in a transaction, possibly in a stored procedure, but this would be rather awkward. However, MySQL has a command called "REPLACE" which I think will do exactly what you want: http://dev.mysql.com/doc/refman/5.0/en/replace.html Good luck! Douglas Sims [EMAIL PROTECTED] On Aug 29, 2006, at 12:08 AM, Ravi Kumar. wrote: > Dear Members, > > I wish to insert and update conditionally into a table using a single > SQL statement. That is if a record already exists in a table, the > statement should update it, else it should insert it. Probably > something like > this: > > if (select * from UserPoints where username = 'ravi') then (update > UserPoints set points = points + 5 where username = 'ravi') else > (insert into UserPoints (username, points) values ('ravi', 0)) end if > > I can not do it pragmatically using PHP, because the environment is > highly concurrent and it may result in a 'race condition' often. > > The version of MySQL we are using is 4.1.14-standard-log. > > Please reply soon, it is urgent. > > Have a nice day! > > Thanks and regards, > > Ravi. -- 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]
