Re: UPSERT

Jonathan Scher wrote:
> Hello,
>
> I'd like to work on TODO item:
> > Add REPLACE or UPSERT command that does UPDATE, or on failure, INSERT
>
> could you please tell me if I'm going in the right way?
>
> There are some different syntaxes possible, but MySQL has an
> interesting one here:
> http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
>
> INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
> This allow to make an insert, and if the key is already there to
> modify the value depending on the current one.
>
> Then I have two choices possible:
> - Search for existing tuples among key or unique constraint, then if
> nothing is found, insert it.
> - Try to insert a new row, catch if there is any error, and then
> search for all tuple matching.
>
> As it would be a new command, I have no idea on what the data could be.
> Does syntax meet your needs? Which choice should I implement?

Good. Some thoughts from the top of the head:

Is "insert or on failure update" semantically equivalent to "update or
on failure insert"? If not the former seems more desirable to me anyway.

What are the syntax alternatives? This one from MySQL doesn't seem too
bad, but it would be good to have them all on the table.

My instinct would be to follow your first strategy, i.e. detect which
path is needed rather than try one and then if it fails do the other.

cheers

andrew