Posted by Rik on 10/25/06 14:53

Captain Paralytic wrote:
> Rik wrote:
>> Chung Leong wrote:
>>> Daz wrote:
>>>> The problem is that when I have a few hundred results to compare.
>>>> Should I really query the database that many times? Could I do it
>>>> with a single query, and if so, how would I know what items the
>>>> user already owns a particular item, and update the database using
>>>> the PHP-MySQL layer. To my understanding, you can't execute and
>>>> UPDATE or INSERT statement from within a SELECT statement. Nor can
>>>> you execute several statements, such as multiple UPDATE statements
>>>> or several INSERT statements all in 1.
>>>
>>> No, that still wouldn't remove the race condition. What you want to
>>> do is put a unique constraint on the table, then have your script
>>> just perform the INSERT. If it fails, then you know you have a
>>> duplicate. MySQL also support the INSERT ... ON DUPLICATE KEY
>>> UPDATE syntax I believe.
>>
>> Yup, or the shorter REPLACE INTO which does exactly the same.
>
> REPLACE INTO does not do exactly the same as INSERT ... ON DUPLICATE
> KEY UPDATE
> REPLACE INTO deletes a row that is already there and replaces it with
> a new row containing values only for fields you supply.

It can even delete several rows to replace with a new row, if the indexes
require it yes.

> INSERT ... ON DUPLICATE KEY UPDATE simply changes any values you ask
> it to on an existing row, leaving any fields that you haven't
> mentioned intact.


Yes, you're right, the inner workings are different. However, when feeding
2 fields to a 2 column table here, the effect will be the same.
--
Grtz

Rik Wasmus

[Back to original message]