Insert and Update ? (newbie)

Hi all,

this is my first port to this group. There are so many requests for help I am surprised that anyone has the time to give any. Thanks in advance to all those that do.

Anyway - here is my question.

I have a table that has a primary key constraint and there will probably be foreign key constrains to it.

I have another table that I have loaded using sql*loader. I want to merge the second table into the first one ie I want to update any matching rows in the first table with all the non key values from the second table and I want to insert all the rows from the second table into the first table if they are not already there.

People must do this so often that there should be a nice ready made way of doing it ?

I have tried a few ways and always come to a dead end :

1. delete all the rows from the first table that exist in the second table and then insert all the rows in the second table ? My delete sql looks horrible because of multiple columns in the primary key eg.

delete from table1 where col1||col2 = (select table2.col1||table2.col2 from table2 where table1.col1 = table2.col1 and table1.col2 = table2.col2);

there must be a better way that that and the delete probably wont work when there are foreign key contraints anyway.

2. update table1 with table2 and then insert all the records from table2 that are not in table1 ?

update table1 set (col3,col4) = (select table2.col3, table2.col4 from table2 wheretable1.col1 = table2.col1 and table1.col2 = table2.col2);

Looks fair enough but when run all the col3s and col4s in rows of table1 that do not match rows from table2 get set to null !!!!!!

3. Select all the rows from table1 that do not match rows from table2 into a new table3 and then insert all the rows from table2 into table3. Then how to get it back into table1 ? I don't think so.

Can someone put me out of my misery ?

thanks,
Mark. Received on Thu Feb 04 1999 - 07:34:36 CST