Getting primary key value after merge command?

Question

Is there a way to get the value from the last inserted or updated row?

I use the merge command to create an insert or update if a row exists. I know how to get the auto-generated key after insertion, but can I get the primary key if I use the merge command?

I am using Java with JDBC and Oracle DB.

Answer 1

As APC says: "Unlike INSERT, the Oracle MERGE syntax does not support the RETURNING clause. Alas."

I am afraid that the simplest answer to this is to implement your business logic as a PL / SQL function that implements your business logic and returns the value you are interested in.

Abort the process in steps in PL / SQL to check if an existing row already exists should not be too slow as you should be (I suppose) by doing a direct search on the primary key (or at least a uniquely indexed column)

Answer 2

Long shot: you can write a trigger in the database that marks any update, or insert and put the timestamp in a new column that you name: the last one changed (or something like that). Then order this column to get the last edited row.

Answer 3

After reading this, having experienced a brief moment of failure, looking for some way to get this work to work with the MERGE operator, and then finally accepting the APC comment, I settled on the following alternative, which may be useful for someone else:

public List upsert(String updateSql, String insertSql, Map data){ List ids = new ArrayList(); Map params = new HashMap(); params.put("name", data.get("name")); if(data.get("personId") != null){ params.put("personId", data.get("personId")); int rowCount = jdbcTemplate.update(updateSql, params); if(rowCount == 1){ ids.add(data.get("personId")); } }else{ keyHolder = new GeneratedKeyHolder(); int rowCount = jdbcTemplate.update(insertSql, params, keyHolder); if(rowCount == 1){ ids.add(keyHolder.getKey().intValue()); } } return ids; } 

Answer 4

I ran into this problem using higher level retention levels. My solution is to do an insert and then get the resulting identifier and update in a row. I do not optimally understand, but I still have to find a better solution.

However, I found that often a combination of non-primary key columns gives a unique row. If you can identify this data in a database, I recommend setting a Unique constraint on columns so that they are unique. If you have another way to access unique strings, you can use it to merge.