Sunday, May 1, 2011

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

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?

From stackoverflow
  • I think you need a whole new approach. You are open to SQL Injection. Provide us with some sample code as to how you are getting your data inputs and sending the statements to the database. alt text

    Matthew Flaschen : I agree. If this is production code, you should be using parameterized statements at the least. Tell us what language you're using, and maybe you could figure out a better approach like object persistence.
    womp : How is this even relevant? The question is about creating strings that work with two statements.
    Rob Elsner : Learning better programming practices is always relevant.
    Jose Basilio : @womp meet humor. Humor meet @womp. A lot of developers are not aware of SQL Injection. This is just a funny way of reminding people about it.
    MrTelly : To paraphrase sport play the question not the man, this is clearly the long known issue of insert/update sql incompatiblity. Yes the strings might be open to attack - but that wasn't the question!
  • 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.

  • 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.

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

  • 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.

  • 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.

  • Thank you everyone for your answers and suggestions :)

  • 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}
    

0 comments:

Post a Comment