Why do SQL INSERT and UPDATE Statements have Different Syntaxes?

While contemplating this question about a SQL INSERT statement, it occurred to me that the distinction in syntax between the two statements is largely artificia开发者_如何学Cl. That is, why can't we do:

INSERT INTO MyTable SET Field1=Value1, Field2=Value2, ...

or

UPDATE MyTable ( Field1, Field2 ...) VALUES ( Value1, Value2, ... ) 
    WHERE some-key = some-value

Perhaps I'm missing something critical. But for those of us who have had to concatenate our SQL statements in the past, having comparable syntax for an INSERT and an UPDATE statement would have saved a significant amount of coding.

They're serving different grammatical functions. In an update you are specifying a filter that chooses a set of rows to which you will apply an update. And of course that syntax is shared with a SELECT query for the same purpose.

In an INSERT you are not choosing any rows, you are generating a new row which requires specifying a set of values.

In an UPDATE, the LHS=RHS stuff is specifying an expression which yields true or false (or maybe null :) and in an INSERT, the VALUES clause is about assignment of value. So while they are superficially similar, they are semantically quite different, imho. Although I have written a SQL parser, so that may influence my views. :)

SQL Server 2008 has introduced UPSERT functionality via the MERGE command. This is the logical equivalent of

IF FOUND THEN
 UPDATE
ELSE
 INSERT

I believe this is so that you may make an insert statement without being explicit about the values. If you are putting a value in every single column in the table you can write:

insert into my_table values ("value1", 2);

instead of:

insert into my_table (column1, column2) values ("value1", 2);

When importing and exporting entire (large) databases, this is invaluable for cutting down file size and processing time. Nowadays, with binary snapshots and the like, it may be "less invaluable" :-)