INSTATE INTO <tablename>
<column> = <value> AS KEY
{, <column> = <value> AS KEY}
{, <column> = <value>}

This is for all those times when you find yourself typing something like
SELECT COUNT(*)
FROM sometable
WHERE some - column(s) - which - may - or - may - not - consitute - an - official - unique - key = some - value(s)

IF [what we just selected] > 0 THEN
UPDATE sometable
SET all - these - columns - no - not - just - the - key - like - columns - some - other - ones = all - these - values

ELSE
INSERT INTO sometable ( all - these - same - columns - both - the - key - like - ones - and - the - other - ones )
VALUES ( the - same - values - as above )

Basically, it just means you type out the list of columns and values only once, and the existence check is implied.

.. and I like "INSTATE" because, in the best traditions of SQL, it's a real English word with almost the right meaning, *and* it looks almost like a cross between INSERT and UPDATE.
-- pertinax, Jan 01 2010

I've wanted this feature on several occasions. [+]

"Create a new record if none exists, else update the existing one" is such a common design pattern that it's strange that SQL doesn't support it.
-- Wrongfellow, Jan 01 2010


I think what you're looking for already exists:

http://dev.mysql.com/doc/

refman/5.0/en/

insert-on-duplicate.html

(JOIN THESE 3 LINES INTO ONE, posting limits word length to 30 characters. Whoever designed this should do a simple insertion of <wbr> tags to force-break words)

INSERT INTO <tablename> VALUES(7,'x') ON DUPLICATE KEY UPDATE col1 = col1+1, col2 = VALUES(2);
-- manixrock, Jan 01 2010


Good for MySQL!

... but if I've read that right, it's not quite as terse as mine, so there. {big asinine grin}
-- pertinax, Jan 01 2010


Also, as far as I can see, this is a MySQL extension, not a part of the portable core of the SQL language. I'm sure other database engines can do this too, in their own way, but some of us prefer to write portable code whenever we can.

SQL - another 'standard' that wasn't. <sigh>
-- Wrongfellow, Jan 02 2010



random, halfbakery