How to return primary key from oracle merge query
merge, oracle, oracle10g, sequence, sql-returning
Solution
There's a problem.
- `Merge Into` doesn't support `Returning Into`, so that won't work.
- The sequence will not always be used, because it's only used when inserting new records.
- Getting the existing value of a sequence won't work, because you get an error if you want to query Sequence.currval when the sequence wasn't used in the current session yet.
To solve it:
- Use a procedure or anonymous program block to try to update the value. If `sql%rowcount` return 0 after the update, perform the insert instead.
- Use the selection (query for `UPPER(TRIM(name))`) to find the record that was updated.
Problem
I want to return the primary key from an oracle merge query. I'm using a single statement to insert if not exist and I don't want to use procedure or function to do so.. this is the sample query ``` merge into myTable e using (select :empname name from dual) s on (UPPER(TRIM(e.empname)) = UPPER(TRIM(s.name))) when not matched then insert (empname) values (s.name) ``` and I need to get another primary key field of the myTable. the primary key is inserted using sequence and trigger I tried adding `RETURNING empID into :empId` but it gives error