Half a croissant, on a plate, with a sign in front of it saying '50c'
h a l f b a k e r y
Bone to the bad.

idea: add, search, annotate, link, view, overview, recent, by name, random

meta: news, help, about, links, report a problem

account: browse anonymously, or get an account and write.

user:
pass:
register,


         

SQL Instate

Either insert or update, depending.
  (+3)
(+3)
  [vote for,
against]

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


Please log in.
If you're not logged in, you can see what this page looks like, but you will not be able to add anything.



Annotation:







       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
  


 

back: main index

business  computer  culture  fashion  food  halfbakery  home  other  product  public  science  sport  vehicle