SQL: Use the same string for both INSERT and UPDATE?

Question

The INSERT syntax I've been using is this

INSERT INTO TableName VALUES (...)

The UPDATE syntax I've been using is

UPDATE TableName SET ColumnName=Value WHERE ...

So in all my code, I have to generate 2 strings, which would result in something like this

insertStr = "(27, 'John Brown', 102)";
updateStr = "ID=27, Name='John Brown', ItemID=102";

and then use them separately

"UPDATE TableName SET " + updateStr + " WHERE ID=27 " +
"IF @@ROWCOUNT=0 "+
"INSERT INTO TableName VALUES (" + insertStr + ")"

It starts bothering me when I am working with tables with like 30 columns.

Can't we generate just one string to use on both INSERT and UPDATE?
eg. using insertStr above on UPDATE statement or updateStr on INSERT statement, or a whole new way?

Answer 1

Some databases have proprietary extensions that do exactly this.

I agree that the syntax of INSERT and UPDATE could be more consistent, but this is just a fact of life now -- it ain't gonna change now. For many scenarios, the best option is your "whole new way": use an object-relational mapping library (or even a weak-tea layer like .NET DataSets) to abstract away the differences, and stop worrying about the low-level SQL syntax. Not a viable option for every application, of course, but it would allow you to just construct or update an object, call a Save method and have the library figure out the SQL syntax for you.

Answer 2

As far as I'm aware, what you're describing isn't possible in ANSI SQL, or any extension of it that I know. However, I'm mostly familiar with MySQL, and it likely depends completely upon what RDBMS you're using. For example, MySQL has "INSERT ... ON DUPLICATE KEY UPDATE ... " syntax, which is similar to what you've posted there, and combines an INSERT query with an UPDATE query. The upside is that you are combining two possible operations into a single query, however, the INSERT and UPDATE portions of the query are admittedly different.

Generally, this kind of thing can be abstracted away with an ORM layer in your application. As far as raw SQL goes, I'd be interested in any syntax that worked the way you describe.

Answer 3

Well, how about no statements? You might want to look into an ORM to handle this for you...

Answer 4

If you think about it, INSERT and UPDATE are exactly the same thing. They map field names to values, except the UPDATE has a filter. By creating an associative array, where the key is the field name and the value is the value you want to assign to the field, you have your mapping. You just need to convert it to a the proper string format depending on INSERT or UPDATE. You just need to create a function that will handle the conversion based on the parameters given.

Answer 5

Some DBMS' have an extension to do this but why don't you just provide a function to do it for you? We've actually done this before.

I'm not sure what language you're using but it's probably got associative arrays where you can wrote something like:

pk{"ID"}   = "27"
val{"Name"} = "'John Brown'"
val{"ItemID"} = "102"
upsert ("MyTable", pk, val)

and, if it doesn't have associative arrays, you can emulate them with multiple integer-based arrays of strings.

In our upsert() function, we just constructed a string (update, then insert if the update failed) and passed it to our DBMS. We kept the primary keys separate from our other fields since that made construction of the update statement a lot easier (primary key columns went in the where clause, other columns were just set).

The result of the calls above would result in the following SQL (we had a different check for failed update but I've put your @@rowcount in for this example):

update MyTable set
    Name = 'John Brown',
    ItemID = 102
    where ID = 27
if @@rowcount=0
    insert into MyTable (ID, Name, ItemID) values (
        27,
        'John Brown',
        102
    )

That's one solution which worked well for us. No doubt there are others.

Answer 6

Thank you everyone for your answers and suggestions :)

Answer 7

SQL Server 2008:

MERGE dbo.MyTable AS T
USING
 (SELECT  
  @mykey AS MyKey
  @myval AS MyVal
  ) AS S

ON (T.MyKey = S.MyKey)

WHEN MATCHED THEN 
  UPDATE  SET 
    T.MyVal = S.MyVal
WHEN NOT MATCHED THEN
  INSERT (MyKey, MyVal)
  VALUES (S.MyKey, S.MyVal)

MySQL:

INSERT (MyKey, MyVal)
INTO MyTable
VALUES({$myKey}, {$myVal})
ON DUPLICATE KEY UPDATE myVal = {$myVal}