Getting the primary key value after a merge command?

4
votes

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

I am using the merge command to do an insert or an update if the row exists. I know how to get the autogenerated key after an insert but can I get the primary key if I use the merge command?

I'm using Java with JDBC and Oracle DB.

4
javaoraclejdbcmergeprimary-key
Presumably you mean the "MERGE INTO <table> USING <expr> ... " syntax for 'upserts'. I don't think you can do it, but you might want to make it clear what you're talking about in the question. - wowest
Unlike INSERT the Oracle MERGE syntax does not support a RETURNING clause. Alas. - APC
What do you mean by the "last inserted or updated row" ? That is to say, the MERGE command does not have an ordering in how it will process the data. I'm not sure how knowing what the last record is in a random arrangement of data is useful. Perhaps you could provide the schema and Merge statement for evaluation? - Adam Hawkes

4 Answers

2
votes

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

I'm afraid 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.

Breaking the process into steps in the PL/SQL to check if the row already exists shouldnt be too much slower as you should be (I presume) doing a direct lookup on a primary key (or at least a uniquely indexed column)

1
votes

Long shot : you could write a trigger in the database that timestamp any update or insert and put the timestamp in a new column that you will name : last modified (or something like that). Then order by this column to get the last edited line.

1
votes

After reading this, experiencing a brief moment of denial, searching for some way to make this work with the MERGE statement, then finally accepting APC's comment, I have settled on the following alternative that may be helpful to 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;
}
0
votes

I've hit this issue using higher level persistence layers. My solution is to do an insert, then get the resulting ID and update into the row. Not optimal I realize but I have yet to find a better solution.

However, I've found that often combination of non-primary key columns will yield a unique row. If you can identity this data in the database I recommend setting an Unique constraint on the columns so they are guaranteed to be unique. Once you have another way of accessing unique rows, you can use that to do your merge.