List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:April 27 2007 11:29pm
Subject:INSERT ... ON DUPLICATE KEY UPDATE seems so close and yet so far...
View as plain text  
Maybe this is some SQL standard implementation and that's why it is what it
is, but to me it seems completely retarded that you have to explicitly call
out the columns... 

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Why doesn't it work in a more convenient and sane way?!

So instead of this cumbersome incantation that makes you want to rip out
your hair and puch your cube-mate dead in the nose:

insert into table1 (c1,c2,c3) values (v1,v2,v3) on duplicate key
update c1=v1, c2=v2, c3=v3; 

Just allow a more sane and logical:

insert into table1 (c1,c2,c3) values (v1,v2,v3) on duplicate key update;

And then mySQL should be smart enough to do the c1=v1 mapping, just like it
can do on a regular insert.  If the columns are called out like that, then
you know what values I was trying to put in them. Honestly it could even
work if I used 'insert into table1 values (v1,v2,v3) on duplicate key
update' as mySQL could just look up all the columns and map accordingly.

It's baffling. I can sorta see how it might be useful to have the current
syntax in the RARE occasion you only want to update a portion of the record
or some other column not called out, like this:

insert into table1 (c1,c2,c3) values (v1,v2,v3) on duplicate key
update c1=v2+v3;

or

insert into table1 (c1,c2,c3) values (v1,v2,v3) on duplicate key
update c4=v4;

But honestly, I can't think of a case that you couldn't achieve it with my
suggestion.

So close and yet so far... 

Can the mySQL guys consider implementing this 'extension' as well? With
InnoDB becoming more useful all the time, and FKs being used, REPLACE INTO
is becoming more and more dangerous to use. So many times we have a
programmer wipe out some important data because of that statement (as it
does a DELETE first, which triggers the constraint!). I thought that the
INSERT INTO...ON DUPE UP was gonna be an easy change, but it's just as
tricky to match up all the columns and honestly it doesn't really get you
all that much once your done typing everything out.

d

Thread
UPDATE and INSERT in onelists20 Apr
  • Re: UPDATE and INSERT in oneJohan Höök20 Apr
    • INSERT ... ON DUPLICATE KEY UPDATE seems so close and yet so far...Daevid Vincent28 Apr
      • Re: INSERT ... ON DUPLICATE KEY UPDATE seems so close and yet sofar...Philip Hallstrom28 Apr
        • RE: INSERT ... ON DUPLICATE KEY UPDATE seems so close and yet so far...Daevid Vincent30 Apr
  • Re: UPDATE and INSERT in oneDavid Precious20 Apr
Re: UPDATE and INSERT in onewang shuming20 Apr