mysql - Portable SQL upsert (insert+update) solution needed -

i need perform simple operation on database - put values table key, if row exists - update it, if not - create new one. problem need in portable way - i.e. same query must work on mysql, sql server, oracle , preferably db2, postgres, etc. while use replace or insert ... on duplicate key update in mysql, i'm not sure these support such syntax. , want avoid if's database type, because unmaintainable. don't want query value before updating, because suspect slow down process (i need multiple times).

so far best came doing:

  update table set data='data' key='key';   insert table(key, data) values ('key', 'data'); 

one of succeed , fail, don't care 1 of queries fails. looks kind of inelegant though. suggestions how make better?

the database-agnostic solution, using modern databases, call update insert in 2 operations. databases not allow multiple statements sent in single operation , databases might not return number of rows affected update not rely upon that.

update mytable set data = 'data' keycol = 'key'; 

(separate call)

insert mytable(keycol, data) select 'key', 'data' ( select 1 value ) z not exists    (                     select 1                     mytable t1                     t1.keycol = 'key'                     );