Hi All,

Bit of an odd one here and I'm hoping someone has a solution.

I have a table which has a sequence based PK and has a merge statment run against it (a simplified version is shown below).

Code:
MERGE INTO databases_users dk
USING (SELECT username, created from dba_users) du
ON (dk.username = du.username)
WHEN MATCHED THEN UPDATE SET dk.created = du.created
WHEN NOT MATCHED THEN INSERT (dk.user_id, dk.username, dk.created)
VALUES (databases_users_s.nextval, du.username, du.created)';
The problem is that even though the sequence is only part of the insert it is still incremented as part of the update statement, which means that the sequence is advance by 2700 each time the merge is run even though no new records are added.

I have looked on Metalink and can find some simialr problems but no solution (apart from creating a trigger to insert the sequence value). Has anyone else hit this issue and found a way round it. I'd like to keep this a part of one statement if possible.

DB Ver is 9.2.0.5 on Sun Solaris

Thanks in advance