Q
Problem solve Get help with specific problems with your technologies, process and projects.

Syntax for merge of two tables

I am trying to run a merge of two tables, inserting a new contact_id from a sequence if a row is inserted. I don't understand the relationship between the function to count up the sequence numbers and the merge command.

I am trying to run a merge of two tables, inserting a new contact_id from a sequence if a row is inserted. I don't understand the relationship between the function to count up the sequence numbers and the merge command. So far I have the following syntax (which is not working):
 create or replace function my_seq_next_val return number is v_next_val number; begin Select my_seq.nextval into v_next_val from dual; Return v_next_val; end; Merge into destination_table d using source_table s on (d.unique_id = s.unique_id) when matched then Update set d.full_name = s.full_name, d.phone = s.phone, d.email = s. email when not matched then insert (d.contact_id, d.creation_date, d.full_name, d.phone, d.email, d.unique_id) values (v_next_val, s.creation_date, s.full_name, s.phone, s.email, s.unique_id); commit;
Is this the right syntax to put a new sequence number in the contact_id field of a newly inserted row, using the merge command? I'm not clear on the relationship between the sequence function and how to call it when the merge command runs.
You do not need to use a function. You can replace v_next_val with my_seq.nextval in your merge statement. For example:
 Merge into destination_table d using source_table s on (d.unique_id = s.unique_id) when matched then Update set d.full_name = s.full_name, d.phone = s.phone, d.email = s. email when not matched then insert (d.contact_id, d.creation_date, d.full_name, d.phone, d.email, d.unique_id) values (my_seq.nextval, s.creation_date, s.full_name, s.phone, s.email, s.unique_id);
This was last published in October 2006

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close