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]