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

Question

The INSERT syntax I used is

INSERT INTO TableName VALUES (...)

The UPDATE syntax that I used is

UPDATE TableName SET ColumnName=Value WHERE ...

So, in all my code I need to generate 2 lines, which will lead to 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 + ")"

This starts to bother me when I work with tables with 30 columns.

Can't create only one row for use in both INSERT and UPDATE?

eg. using insertStr above in an UPDATE statement or updateStr in an INSERT statement or a completely new way?

Answer 1

, , ? .

, , , , , - :

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

, , .

upsert() (update, insert, update ) . , ( where, ).

SQL ( update, @@rowcount ):

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
    )

, . , .

Answer 2

, . SQL Injection. , . alt text http://goose.ycp.edu/~weddins/440/S09%20IFS440%20Bobby%20Drop%20Tables.PNG

Answer 3

, , , ANSI SQL , . , MySQL, , , , . , MySQL "INSERT... ON DUPLICATE KEY UPDATE...", , , INSERT UPDATE. , , INSERT UPDATE .

, ORM . raw SQL, , , .

Answer 4

, - ? , ORM, ...

Answer 5

, .

, INSERT UPDATE , - . " ": - ( , .NET DataSets), SQL . , , , "" SQL .

Answer 6

, INSERT UPDATE - . , , UPDATE . , - , - , , . INSERT UPDATE. , .

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}