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.
Message
From: Douglas Sims [mailto:doug (AT) apley (DOT) com]
Sent: Tuesday, 29 August, 2006 10:48 AM
To: Ravi Kumar.
Cc: mysql (AT) lists (DOT) 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:
Good luck!
Douglas Sims
Doug (AT) Apley (DOT) com
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.

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:

mysqlshow create table ravitest;
+
+

+
| Table | Create
Table

|
+
+

+
| ravitest | CREATE TABLE `ravitest` (
`username` varchar(16) NT NULL,
`points` int(10) unsigned default NULL,
PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+
+

+
1 row in set (0.00 sec)

mysqlshow create procedure r1;
++
+

+
| Procedure | sql_mode | Create
Procedure

|
++
+

+
| r1 | | CREATE PRCEDURE `r1`(n VARCHAR(16), p INT)
BEGIN
DECLARE RowsFound INT DEFAULT 0;
SELECT ****(*) INT RowsFound FRM ravitest WHERE username=n;
IF RowsFound=0 THEN
INSERT INT ravitest VALUES (n, p);
ELSE
UPDATE ravitest SET
+p where ravitest.username=n;
END IF;
END |
++
+

+
1 row in set (0.00 sec)

mysqlselect * 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)

mysqlcall r1('ravi', 15);
Query K, 1 row affected (0.00 sec)

mysqlselect * 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)

mysqlcall r1('Elaine', 5);
Query K, 1 row affected (0.00 sec)

mysqlselect * 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
Doug (AT) Apley (DOT) com

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.

Message
From: Douglas Sims [mailto:doug (AT) apley (DOT) com]
Sent: Tuesday, 29 August, 2006 10:48 AM
To: Ravi Kumar.
Cc: mysql (AT) lists (DOT) 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:

Good luck!

Douglas Sims
Doug (AT) Apley (DOT) com
>
>
>
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.
>
>

Douglas Sims wrote:

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:

Why not use this

INSERT INT table
VALUES()
N DUPLICATE KEY UPDATE
X = $X, y=$y