I am using the merge statement to correct a user input error. A customer was entered in the system as Jay Strumpet and should have been Jayleen Strumpet.The user continued to enter data on the incorrect customer, Jay. And occasionally entered some info on Jayleen. The following works:
merge into customer tgt
using ( select *
from customer
where CUSTID = 45566
) src
on ( tgt.CUSTID =66554422
)
when matched
then
update set
tgt.SSN=nvl(tgt.SSN,src.SSN),
tgt.FNAME=nvl(tgt.FNAME,src.FNAME),
tgt.LNAME=nvl(tgt.LNAME,src.LNAME),
tgt.PFX_ID=nvl(tgt.PFX_ID,src.PFX_ID),
tgt.SFXID=nvl(tgt.SFXID,src.SFXID);
But, this isn't all of the fields in the customer table. There are 30 other fields. I need a script that will dynamically create all of the field names for
both the target and the source to prevent any hard coding as this is very time-consuming and I can use such a utility for other similar cases. How, can I do this(in SQL) if I just provide a table name and primary key(s). Also, if the target does not exist, insert a new record. So, given:
table name - ADDRESS,ADDRESS.pk,ADDRESS.street,ADDRESS.zip,ADDRESS.city
Do I use the ALL_COLUMN_TABS where column_name = 'ADDRESS' and get a list of all of the columns then somehow structure the results for my needs? This is the tricky part. Any direction would be appreciated. Please don't forget the INSERT. Thanks!