h a l f b a k e r yBone to the bad.
add, search, annotate, link, view, overview, recent, by name, random
news, help, about, links, report a problem
browse anonymously,
or get an account
and write.
register,
|
|
|
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.
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. |
|
| |
I think what you're looking for already exists: |
|
| |
http://dev.mysql.com/doc/ |
|
| |
(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); |
|
| |
... but if I've read that right, it's not quite as terse as mine, so there. {big asinine grin} |
|
| |
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> |
|
| |