2

I need to perform a very simple operation on the database - put some values into the table by key, if the row exists - update it, if not - create a new one. The problem is that I need to do it in a portable way - i.e. the same query must work on MySQL, SQL Server, Oracle and preferably also DB2, Postgres, etc. So while I could use REPLACE or INSERT ... ON DUPLICATE KEY UPDATE in MySQL, I'm not sure that these would support such syntax. And I really want to avoid if's by database type, because this would be unmaintainable. I also don't want to actually query the value before updating, because I suspect it would slow down the process significantly (I need to do it multiple times).

So far the best I came up with is just doing:

  UPDATE table SET data='data' WHERE key='key';
  INSERT INTO table(key, data) VALUES ('key', 'data');

one of those would always succeed and another fail, but I don't care that one of the queries fails. It looks kind of inelegant though. Any suggestions how to make it better?

StasM
  • 10,593
  • 6
  • 56
  • 103

3 Answers3

5

The only completely database-agnostic solution, using somewhat modern databases, is to call the Update then the Insert in two operations. Some databases do not allow for multiple statements sent in a single operation and some databases might not return the number of rows affected by the Update so I would not rely upon that.

Update MyTable
Set Data = 'Data'
Where KeyCol = 'key';

(separate call)

Insert Into MyTable(KeyCol, Data)
Select 'key', 'Data'
From ( Select 1 As Value ) As Z
Where Not Exists    (
                    Select 1
                    From MyTable As T1
                    Where T1.KeyCol = 'key'
                    );
Thomas
  • 63,911
  • 12
  • 95
  • 141
  • The last time I ran into a database that did not return the number of rows affected was in the 80's ;) Your solution is a good alternative, however. – WReach Dec 21 '10 at 02:13
  • @WReach - Granted, but the OP wasn't exactly explicit in how database-agnostic the solution needed to be. :) – Thomas Dec 21 '10 at 02:14
  • I know that @StasM mentioned they don't want to "query the value before updating, because I suspect it would slow down the process" but isn't that a viable alternative if I don't have such performance concerns. Does this approach have another drawbacks I should know about? – Juraj Martinka Oct 20 '20 at 09:11
  • It is a viable option. Most database products will let you check the row count after the update option which will tell you if you can skip the insert operation. There other vendor specific options like the Merge command in SQL Server which provide some alternatives if you are fine with using a vendor specific solution. – Thomas Oct 21 '20 at 23:41
2

Execute the UPDATE first. Your client code will be informed as to the number of rows updated. If the updated row count is zero, then execute the INSERT.

The exact way that the updated row count is returned to your client code depends upon which database access library you are using. For example, in Java it is the return value of PreparedStatement.executeUpdate() and in the Python DB-API it is accessed using cursor.rowcount.

You will need to make sure that the whole process takes place within a serializable transaction to avoid conflicts with concurrent writers.

WReach
  • 18,098
  • 3
  • 49
  • 93
  • yes, looks like affected row counts are universally supported so I can make it portable enough, thanks. – StasM Dec 21 '10 at 02:03
  • One wrinkle here - mysql seems to return 0 rows affected if the value exists but was set to the same value as it already had. – StasM Nov 16 '11 at 00:46
  • Fair enough. Now... where did I leave that Greatest Hits of the 80's CD? :) – WReach Nov 16 '11 at 15:20
1

MERGE and WHEN NOT MATCHED are part of ANSI SQL 2003. Except of course its not uniformly supported.

Your solution while clever is dangerous because its not clear what the client should do. Ignore all errors? If so what happens if when they both fail.

Instead don't try to make this portable. Use stored procedures and/or a DAL to deal with differences between backends, since this undoubtedly won't be your only problem in any case.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • The client would ignore errors in those statements. if they both fail, it's no big deal, though of course it's the inelegant part there. Unfortunately, stored procedures in not an option here... – StasM Dec 21 '10 at 01:47