Re: Insert and Update ? (newbie)

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

insert into table1 (col1,col2,col3,col4) (select col1, col2,col3,col4 from table2 where
not exists (select 1 from table1 where table1.col1 = table2.col1 and table1.col2 = table2.col2));

HTH Philippe

Mark wrote in message ...
>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 - 11:53:58 CST