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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
