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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
