0

I need to make an sql query which tries to update a row by primary key and if it doesn't exist insert it. The solution needs to be universal for most databases (MySQL, H2, Postgres, Oracle, DB2, MSSQL, Hsql, Derby).

For code examples please consider a simple table consisting of two columns: id (Pr.Key) and txt

Nikita Volkov
  • 42,792
  • 11
  • 94
  • 169
  • 2
    The name for this is `UPSERT`. It is commonly achieved with `MERGE` but this is by no means universally available. Can you be more specific about "most databases"? – Martin Smith Dec 17 '11 at 21:02
  • 1
    @Oded Nothing. I don't know what to try – Nikita Volkov Dec 17 '11 at 21:02
  • @MartinSmith I know a `REPLACE` statement in MySQL which does exactly what I want but it only works with MySQL, so local solutions don't fit – Nikita Volkov Dec 17 '11 at 21:05
  • 2
    @NikitaVolkov You're only going to have a local solution. Even if you use a common syntax, such as update followed by a conditional insert, you will still have to respect locking issues that are different among databases, otherwise you will get a primary key violation. See, for instance, [this question](http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there). – GSerg Dec 17 '11 at 21:12
  • @MartinSmith MySQL, H2, Postgres, Oracle, DB2, MSSQL, Hsql, Derby – Nikita Volkov Dec 17 '11 at 21:19

3 Answers3

3

You want SQL's MERGE statement, but it doesn't work everywhere:

MERGE into thetable using (select theid from thetable)
when matched then update thetable thecolumn = thevalue where id = theid
when not matched then insert into thetable blablabla;

The other solution is to try and insert anyway, handle the error, detect whether it is a constraint violation and if so update instead.

fge
  • 119,121
  • 33
  • 254
  • 329
  • 1
    Well, it is the most universal I can come up with given the requirements... Another solution would be to try and insert anyway, but then it would mean detecting that the error is a unique key constraint violation! – fge Dec 17 '11 at 21:30
0

You have to use a stored procedure to do this. See : http://jumpingbean.co.za/blog/mysql-if-exists-update-else-insert

  • Could you please be more specific? What would a query look like if for instance the table consisted of two columns: id (Pr.Key) and txt? – Nikita Volkov Dec 17 '11 at 21:06
0

Maybe:

INSERT INTO table (id,txt) VALUES (1,"Hello")
  ON DUPLICATE KEY UPDATE txt="World";

But this is MySQL only

SlavaNov
  • 2,447
  • 1
  • 18
  • 26