[TYPO3-dev] Should t3lib_db support MySQL's REPLACE INTO statement?

[ries van Twisk]

On Nov 13, 2008, at 4:12 AM, Steffen Kamper wrote:

> Hi Xavier,
>
> Xavier Perseguers schrieb:
>> Hi Steffen,
>>> Reading the comments in the doc it seems not to be unproblematic, eg
>>> that it makes a delete and the innoDB issues.
>>> I also don't understand the difference between REPLACE and  
>>> INSERT ...
>>> ON DUPLICATE KEY UPDATE, does anyone?
>>
>> When you perform a REPLACE on a non existing row, then you INSERT it.
>> Thus it's the same as writing INSERT INTO...
>>
>> When you perform a REPLACE on an existing row, you would like  
>> instead to
>> perform an UPDATE but MySQL does not bother to check whether the row
>> exists, it DELETEs it then performs an INSERT. This leads to loose  
>> all
>> associated data (with constraints and ON DELETE CASCADE statements)
>> whereas you just wanted to UPDATE some row.
>>
>> Writing INSERT INTO ... ON DUPLICATE KEY UPDATE means that whenever  
>> you
>> try to INSERT a record with a primary key that is already there,  
>> instead
>> of having a constraint error, the statement should automatically be
>> converted to an UPDATE statement, which is really nice and I think  
>> far
>> better than the original REPLACE statement.
>>
>
> thx for the explanation.
>
> So i would prefer the INSERT_UPDATE method which should be the better
> alternative with same result.
>
> vg Steffen

I think SQL standard would call the insert/update statement a MERGE

PostgreSQL doesn't have them but can easily be wrapped in a  
transaction given that the API ingo is planning to make is correct.

Ries