Dear All, Thanks for the prompt replies. The version of MySQL we are using (4.1.14-standard-log) supports INSERT ON DUPLICATE, hence I am using this. Have a fantastic day! Regards, Ravi.
_____ From: Douglas Sims [mailto:[EMAIL PROTECTED] Sent: Tuesday, 29 August, 2006 12:02 PM To: Johan Höök Cc: Ravi Kumar.; mysql@lists.mysql.com Subject: Re: Conditional Insert Much better. Good job. Douglas Sims [EMAIL PROTECTED] On Aug 29, 2006, at 1:26 AM, Johan Höök wrote: Hi Ravi, you can take a look at: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html which might take care of your problem. /Johan Douglas Sims skrev: Hi Ravi You are correct. I was just sitting here thinking about this after I sent that and realized that what I said was incorrect; the "REPLACE" command will not do what you want, because it delete a record instead of updating it, it has no way to know what the previous value is. You probably should define a stored procedure and call that. Here is an example which seems to work: mysql> show create table ravitest; +----------+---------------------------------------------------------------- ---------------------------------------------------------------------------- -------------------------------+ | Table | Create Table | +----------+---------------------------------------------------------------- ---------------------------------------------------------------------------- -------------------------------+ | ravitest | CREATE TABLE `ravitest` ( `username` varchar(16) NOT NULL, `points` int(10) unsigned default NULL, PRIMARY KEY (`username`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +----------+---------------------------------------------------------------- ---------------------------------------------------------------------------- -------------------------------+ 1 row in set (0.00 sec) mysql> show create procedure r1; +-----------+----------+---------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------------------------+ | Procedure | sql_mode | Create Procedure | +-----------+----------+---------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------------------------+ | r1 | | CREATE PROCEDURE `r1`(n VARCHAR(16), p INT) BEGIN DECLARE RowsFound INT DEFAULT 0; SELECT COUNT(*) INTO RowsFound FROM ravitest WHERE username=n; IF RowsFound=0 THEN INSERT INTO ravitest VALUES (n, p); ELSE UPDATE ravitest SET ravitest.points=ravitest.points+p where ravitest.username=n; END IF; END | +-----------+----------+---------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from ravitest; +----------+--------+ | username | points | +----------+--------+ | Ravi | 3 | | Joe | 7 | | Amy | 9 | | Doug | 22 | | Susan | 4 | | Tom | 2 | | Jim | 8 | +----------+--------+ 7 rows in set (0.00 sec) mysql> call r1('ravi', 15); Query OK, 1 row affected (0.00 sec) mysql> select * from ravitest; +----------+--------+ | username | points | +----------+--------+ | Ravi | 18 | | Joe | 7 | | Amy | 9 | | Doug | 22 | | Susan | 4 | | Tom | 2 | | Jim | 8 | +----------+--------+ 7 rows in set (0.00 sec) mysql> call r1('Elaine', 5); Query OK, 1 row affected (0.00 sec) mysql> select * from ravitest; +----------+--------+ | username | points | +----------+--------+ | Ravi | 18 | | Joe | 7 | | Amy | 9 | | Doug | 22 | | Susan | 4 | | Tom | 2 | | Jim | 8 | | Elaine | 5 | +----------+--------+ 8 rows in set (0.00 sec) Good luck! Douglas Sims [EMAIL PROTECTED] On Aug 29, 2006, at 1:01 AM, Ravi Kumar. wrote: 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] ------------------------------------------------------------------------ No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.405 / Virus Database: 268.11.6/430 - Release Date: 2006-08-28 <johan.hook.vcf>
