Please log in.
Before you can vote, you need to register. Please log in or create an account.
Computer: Data Organization
SQL Instate   (+3)  [vote for, against]
Either insert or update, depending.

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
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

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:



(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