h a l f b a k e r yWith moderate power, comes moderate responsibility.
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,
|
|
|
Please log in.
Before you can vote, you need to register.
Please log in or create an account.
|
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.
[link]
|
| |
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> |
|
| |